Table person
id | name | age
---------------
1 | max | 30
2 | sue | 28
3 | ada | 22
4 | sam | 31
5 | test | 0
Table male
id | personid
--------------
1 | 1
2 | 4
3 | 5
Table female
id | personid
--------------
1 | 2
2 | 3
3 | 5
How can I get an output like this? So a column (gender) should be related to the presence in another table and the output.
name | gender
-------------
max | M
sue | F
ada | F
sam | M
test | X
You can use CASE statement to look up from the gender tables as:
SELECT a.name,
CASE WHEN EXISTS (SELECT personid FROM TableMale b WHERE b.personid = a.ID) THEN 'M'
WHEN EXISTS (SELECT personid FROM TableFemale c WHERE c.personid = a.ID) THEN 'F'
END AS Gender
FROM TablePerson a
'X'. It does not correctly answer the question.Try something like this with nested IIF statements:
SELECT p.[Name], IIF(m.ID IS NOT NULL AND f.ID IS NOT NULL, 'X', IIF(m.ID IS NOT NULL, 'M', 'F')) AS Gender
FROM Person p
LEFT OUTER JOIN Male m ON p.ID = m.PersonID
LEFT OUTER JOIN Female f ON p.ID = f.PersonID
Personid 5 is both male and female. I would not want to lose this information. So:
select p.name,
(case when m.personid is not null and f.personid is not null
then 'X' -- both
when m.personid is not null
then 'M'
when f.personid is not null
then 'F'
end) as gender
from person p left join
male m
on p.id = m.personid left join
female f
on p.id = f.personid;
