I am using a left outer join in a select statement as
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
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
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