Doug Blank Doug Blank - 7 months ago 12
SQL Question

SQL JOIN on one field or the other

Trying to order a family by father's name or, if there is no father, then the mother's name where the names are in a separate "person" table, something like:

SELECT DISTINCT family.myid FROM family
JOIN person
ON family.father_id = person.myid OR
family.mother_id = person.myid
ORDER BY person.surname,
person.given_name;


In this version, the families without fathers end up unsorted at the bottom. Would like families without fathers to appear in the order by the mother's name. Sqlite SQL will suffice.

Answer

Basically, you need a separate join for the fathers and the mothers:

select f.*
from family f left join
     person d
     on f.father_id = d.myid left join
     person m
     on f.mother_id = m.myid
order by (case when d.myid is null then m.surname else d.surname end),
         (case when d.myid is null then m.given_name else d.given_name end);

Because a value could be missing, this should be a left join.