Left join help

• Guras

  SSCertifiable

  Points: 6518

  More actions

  January 25, 2018 at 2:06 pm

  #326506

  SELECT  tb1.* 
   from
   tbl1
   inner join tbl2
    on tbl1.Col1= tb2.Col1
   and tbl1.Col2 = tbl2.Col2
   left outer join  tbl3
   on tbl2.Col3 = tbl3.col3 
   where tbl1.FileId = 1 
   -- and tbl3.Col3  = 1234 

  In the above query I want all the data from tbl1 for all matching and non matching records, The above query work fine if I do not add the second clause in the where clause. As soon as I add the second clause it does not return all the rows from the first table.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 25, 2018 at 2:17 pm

  #1977436

  You're filtering out any rows where tbl3.Col3 <> 1234, including those rows where there is no match and tbl3.Col3 is null. Move the condition to the join clause.

  SELECT tb1.*
FROM
tbl1
INNER JOIN tbl2
    ON tbl1.Col1= tb2.Col1
        AND tbl1.Col2 = tbl2.Col2
LEFT OUTER JOIN tbl3
    ON tbl2.Col3 = tbl3.col3
        AND tbl3.Col3 = 1234 -- moved to JOIN
WHERE tbl1.FileId = 1


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA