CODEWITHSUNDEEP
sql-servert-sql
Warda
Warda

Reputation: 67

Query not returning NULL values - SQL

I've been trying for awhile by changing my query around but no LUCK! I have tables:

Demographic
( name, gender, nationality, ethnicity etc)
Reference
(ID, Code as varchar, Description varchar)

Basically I add all my values in the reference table like nationality, Gender, ethnicity then I link it to demographics, eg. ID:1 , Code: Gender , Description: Male

So in demographics any male will have value 1 ( the Reference.ID)

I've written this query:

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d, Reference e, Reference a
WHERE (d.Nationality = e.ID OR d.Nationality IS NULL) AND (d.Gender = a.ID OR 
d.Gender IS NULL)

Without the IS NULL parts it works but it excludes all NULL values. But when I add IS NULL it returns inaccurate values like in Gender column I will have ethnicity values.

Any help will be appreciated.

Upvotes: 0

Views: 2551

Answers (4)

Bhavin Gosai
Bhavin Gosai

Reputation: 211

Try following query: 

    Select Id, fname, Surname
             ,e.Description as Nationality
             ,a.Description as Gender
    FROM Demographics d, Reference e, Reference a
    WHERE   (d.Nationality = e.ID OR (e.ID IS NULL AND d.Nationality IS NULL)) 
    AND     (d.Gender = a.ID OR (a.ID IS NULL AND d.Gender IS NULL))

I have tweaked your query a little. I think you are on the right path, just missed a little, while comparing with NULL.

When you check for one of the operand for nullability (i.e. IS NULL) and miss the other one, query give you all on that missing other side.

Check out this Microsoft TechNet article for further learning: NULL Comparison Search Conditions - TechNet - Microsoft

Upvotes: 0

Thom A
Thom A

Reputation: 95554

This is quite the guess here, however, I think you're problem is that you're writing SQL like you're in the 1980's. As I said in my comment, JOIN syntax has been around for decades. Stop using delimited lists for your tables and using implicit joins.

Anyway, I think what you need is a LEFT JOIN:

SELECT d.Id, d.fname, d.Surname,
       n.[Description] AS Nationality,
       g.[Description] AS Gender
FROM Demographics d
     LEFT JOIN Reference n ON d.Nationality = n.ID
     LEFT JOIN Reference g ON d.Gender = g.ID;

If this isn't correct, then sample and expected results are going to be needed.

Upvotes: 2

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

You need a LEFT JOIN here

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d
    LEFT JOIN Reference e ON d.Nationality = e.ID
    LEFT JOIN Reference a ON d.Gender = a.ID

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

Switch to non-deprecated join style and everithing will go right way:

Select Id, fname, Surname
         ,e.Description as Nationality
         ,a.Description as Gender
FROM Demographics d
LEFT JOIN Reference e 
  ON e.ID = d.Nationality
LEFT JOIN  Reference a
  ON a.ID = d.Gender

Upvotes: 0

Related Questions