Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Behavior of conditions with null left outer join

Status
Not open for further replies.

aamkumar

Programmer
Apr 3, 2007
18
US
I am using a left outer join in a select statement as

Code:
select CR.MKTG_CD_ID, CC.Name_ID, CC.Name, VCR.DOB

from CC

INNER JOIN VCR on VCR.CONTACT_ID=CC.CONTACT_ID

LEFT OUTER JOIN CNTC_MKTG_RESP 	CR on
CR.CNTC_GROUP_ID=VCR.CNTC_GROUP_ID 
and CR.MKTG_CD_ID<> 8248

WHERE CONDITIONS.....


In this above case I get records for CR.MKTG_CD_ID<>8248 including null value records, but I am also getting record where CR.MKTG_CD_ID=8248 ( Actually it should be removed from the record output )as Null value in CR.MKTG_CD_ID column for that record.

BUT if I define the condition as

Code:
LEFT OUTER JOIN CNTC_MKTG_RESP 	CR on
CR.CNTC_GROUP_ID=VCR.CNTC_GROUP_ID 

where (CR.MKTG_CD_ID is null or CR.MKTG_CD_ID<> 8248)

I get all the records for which CR.MKTG_CD_ID<>8248, Null value records for CR.MKTG_CD_ID and the records are not repeated with NULL/Blank value where CR.MKTG_CD_ID=8248

I am not sure why it is giving me this behavior is SQL SERVER 2000

Please advise
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top