title: Sql Query to join two table with tricky logic
body: <p>I have 2 tables mst_item and time_factor , mst_item has 3 columns item,location,card and time_factor has 2 columns location and card</p>
  
  <p>when i am trying to join these two table using location and card columns.</p>
  
  <p>The requirement is below,</p>
  
  <p>mst_item</p>
  
  <pre><code>item     location    card
  xyz       R10        CRU
  ABC       R10        LAT
  CCC       R14        NAC
  </code></pre>
  
  <p>time_factor</p>
  
  <pre><code>location     card 
  R10          CRU
  R10          ALL
  R14          ALL
  R15          FX
  R15          ALL
  </code></pre>
  
  <p>The output should come as, if from mast_item table location and card both match with
  location and card in time_factor table then it should return matching records,</p>
  
  <p>ex-  for R10 &amp; CRU</p>
  
  <pre><code>item     location    card
  xyz       R10        CRU
  </code></pre>
  
  <p>if only location matches then it should return location and 'ALL' as card from time_factor table.</p>
  
  <p>In any case it should not return both matching card value and 'ALL'.</p>
  
  <p>ex- for R14 and NAC</p>
  
  <pre><code>item     location    card
  CCC       R14        ALL
  </code></pre>
  
  <p>Please help me with the query logic.</p>