Replicate Left join on multiple fields in solr
<p>I have a mysql query:</p>

<pre><code>select t1.to_step,count(t1.to_step) from tmp t1 left join tmp t3 on 
(t1.to_step = t3.from_step and t1.applicant_id=t3.applicant_id)
where t3.to_step is null group by t1.to_step
</code></pre>

<p>I am trying to do the above in solr using joins. I know joins in solr work like nested query but i am not able to figure out a proper way to get all the records as i get from the mysql query.</p>

<p>below is what i am using:</p>

<pre><code>q: "-_query_:\"{!join from=from_step_s to=to_step_s}from_step_s:[* TO *]\"",
</code></pre>

<p>This gives me partial set of results. Basically my solr document consists of  fields <code>applicant_id</code>, <code>from_step_s</code> and <code>to_step_s</code> and I want to get the document where a join from a <code>to_step_s</code> to <code>from_step_s</code> doesn't exist for a particular set of <code>applicant_id</code>. I think the problem is somewhere because of the <code>applicant_id</code>join not done in the solr query (which i dont know how to do) because of which the <code>from_step_s</code> of one document gets matched to <code>to_step_s</code> of a different document with different <code>applicant_id</code>.</p>