title: SQL | Join two tables and get one of the tables column against any of the matching row
body: <p>Table A stores site-article wise transfer/transit stock.</p>
  
  <p>Table A</p>
  
  <pre><code>    site     article     transfer_Stock     transit stock
      s1        a1            10                15
      s1        a2            20                25
  </code></pre>
  
  <p>Table B has site-article-storage location wise stock</p>
  
  <p>Table B</p>
  
  <pre><code>    site     article     sloc     stock
      s1        a1         1000      5
      s1        a1         2000      10
      s1        a1         3000      15
      s1        a2         1000      20
      s1        a2         5000      25
  </code></pre>
  
  <p>I want to get total stock as for site article</p>
  
  <p>Output required -</p>
  
  <pre><code>   site     article     sloc     transfer_stock    transit_stock    stock
     s1        a1         1000         10               15             5
     s1        a1         2000          0                0             10
     s1        a1         3000          0                0             15
     s1        a2         1000         20               25             20
     s1        a2         5000          0                0             25
  </code></pre>
  
  <p>I want transfer stock and transit stock against any of the row of sloc.</p>
  
  <p>Table  A has 1 billion rows and Table B has 1.5 billion rows.</p>
  
  <p>what is the optimised way to achieve this in sql.</p>
  
  <p>Currently we are achieving this throguh</p>
  
  <pre><code>SELECT A.site, 
  A.article, 
  B.sloc, 
  case when MIN_B.sloc=B.sloc then A.transfer_stock else 0 end as transfer_stock,
  case when MIN_B.sloc=B.sloc then A.transit_stock else 0 end as transit_stock, 
  B.stock
  FROM A
  LEFT OUTER JOIN B ON A.site = B.site AND A.article = B.article
  LEFT OUTER JOIN (select site,article,min(sloc) from B group by site,article) MIN_B on A.site = MIN_B.site AND A.article = MIN_B.article
  </code></pre>