samedi 30 avril 2016

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.

Aucun commentaire:

Enregistrer un commentaire