Query not returning NULL values - SQL

Warda

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.

Larnu

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related