Why is this Left Join Failing?

[txdave35]

Hey guys,

Well I guess I need to go back to basics here because I'm just not understanding LEFT JOINS apparently. Almost all my queries use inner joins so I rarely have to venture outside of that until today.
I am trying to join my payments table to the history table. However, most people do not have a record in the payments table (anty_pymt). I want my report to list everyone even if they don't have a payment and just show NULL.

I thought LEFT JOIN would do this, but my record counts drops from 300 to 109 when I do the join. If I do RIGHT JOIN, it gives me every single record in the payments table.
What am I missing here? Any help is really appreciated!

SELECT   Distinct a.MBR_SSN_NBR,
         c.ANTY_PYMT_DT,
         c.ANTY_PYMT_TOT_AMT
FROM     DSNP.PR01_T_MBR_HIST a 
         LEFT OUTER JOIN DSNP.PR01_T_ANTY_PYMT c ON a.MBR_SSN_NBR=c.RECIP_SSN_NBR
WHERE    a.AGTY_ID_CD in ('00001', '00005', '00002')
AND      NOT EXISTS (SELECT * FROM DSNP.PR01_T_MBR_HIST B WHERE A.MBR_SSN_NBR = B.MBR_SSN_NBR
     AND B.AGTY_ID_CD not in ('00001', '00005', '0002') )
AND      c.anty_pymt_dt = (select max(d.anty_pymt_dt) from dsnp.pr01_t_anty_pymt d where d.recip_ssn_nbr = c.recip_ssn_nbr)

 

[gmmastros]

In this case, your left join is behaving like an inner join because you are filtering on a table from the right table.

Specifically...

WHERE    Blah blah blah
AND      c.anty_pymt_dt = (select max(d.anty_pymt_dt) from dsnp.pr01_t_anty_pymt d where d.recip_ssn_nbr = c.recip_ssn_nbr)

When you left join a table, and there is no match, the right table in the join returns nulls. Since you basically have column = null (which never returns true), the row is filtered out.

Basically it's this... when you have a left join, then you should put the filtering criteria in the ON clause, not the where clause.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[txdave35]

Thanks George. I figured it was something simple.