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.
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.
Comments