SQLite Join Help

Consider this table structure

people{id,firstName,lastName,preferredName}
list{id,person1_id,person2_id}

I want to get all data from list but with the names of the persons instead of IDs and with a twist: if preferredName is set, use that, otherwise use firstName+SPACE+lastName.

The query if I only needed one name would be this:

SELECT list.id,
CASE WHEN people.preferredName IS NULL OR people.preferredName="" THEN people.firstName||' '||people.lastName ELSE people.preferredName END AS preferredName 
FROM list LEFT JOIN people ON list.person1_id=peopl开发者_运维百科e.id;

Thank you.

COALESCE() returns its first non-null argument.

COALESCE(preferredName, firstName || ' ' || lastName) as person_name

I don't think you need left joins, but I could be wrong. (If you have a foreign key constraint such that person1_id and person2_id must exist in people, you don't need a left join.)

SELECT list.id,
COALESCE( p1.preferredName, p1.firstName || ' ' || p1.lastName) as person1_name,
COALESCE( p2.preferredName, p2.firstName || ' ' || p2.lastName) as person2_name,
FROM list 
INNER JOIN people p1 ON list.person1_id= p1.id
INNER JOIN people p2 on list.person2_id = p2.id

Use table aliases to let you join the same table twice.

SELECT list.id,
       CASE WHEN people1.preferredName IS NULL OR people1.preferredName = ""
         THEN people1.firstName || ' ' || people1.lastName
         ELSE people1.preferredName
       END AS preferredName1,
       CASE WHEN people2.preferredName IS NULL OR people2.preferredName = ""
         THEN people2.firstName || ' ' || people2.lastName
         ELSE people2.preferredName
       END AS preferredName2,
  FROM list
  LEFT JOIN people people1 ON list.person1_id = people1.id
  LEFT JOIN people people2 ON list.person2_id = people2.id