SQLTeam.com | Weblogs | Forums

Accept NULL



I have the following code;

SELECT     dbo.ExtractBtch4bReferral.ReferralGuidDigest, dbo.ExtractBtch4bReferral.PatientGuidDigest, dbo.ExtractBtch4bReferral.Term, dbo.ExtractBtch4bReferral.ConsultationGuidDigest
FROM         dbo.ExtractBtch4bReferral LEFT OUTER JOIN
                      EMISNov16DB.dbo.Consultation ON dbo.ExtractBtch4bReferral.ConsultationGuidDigest = EMISNov16DB.dbo.Consultation.ConsultationGuidDigest LEFT OUTER JOIN
                      EMISNov16DB.dbo.Referral ON dbo.ExtractBtch4bReferral.ReferralGuidDigest = EMISNov16DB.dbo.Referral.ReferralGuidDigest
WHERE     (EMISNov16DB.dbo.Referral.ReferralGuidDigest IS NULL) AND (EMISNov16DB.dbo.Consultation.ConsultationGuidDigest IS NOT NULL) 

The problem I have is;

On top of the WHERE CLAUSE, I want NOT to ignore any records from
EMISNov16DB.dbo.Consultation.ConsultationGuidDigest where the field value is NULL.
I do have NULL values for ConsultationGuidDigest and they shouldn't be neglected, ONLY records that have ConsultationGuidDigest (not null) that exists in EMISNov16DB.dbo.Consultation.

If I say IS NULL - I miss all the duplicates i.e., ConsultationGuidDigest present in dbo.ExtractBtch4bReferral.ConsultationGuidDigest that already exists in

EMISNov16DB.dbo.Consultation.ConsultationGuidDigest is FK char(64) null data type
Therefore, it already have alot of NULL values not necessary duplicates.

How can I update the script please?



Please provide:

  • table description in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide


dordzenz - What do you mean ?



try this,

put your table EMISNov16DB.dbo.Consultation.ConsultationGuidDigest b4 your left join..

it will shows all records


dordzenz - What do you mean ?