Use different columns for text in a form list based on column content

[jdsaljdsajdl]

I’m making a form in Base (with HSQLDB), and i would like to have a drop down list of people with their forename, middle name (if they have one) and surname. I want the list to display the full name, but the value in the form to be the ID. But, i get an empty line in the list for people without a middle name when using this query in the “List content” field:

SELECT “Middlename” || ’ ’ || “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC

I have these columns in table “People”:
ID (automatically assigned)
Forename
Middlename (optional)
Surname

Would there be a way to use two different queries, one if the column Middlename is NULL, and one if it has any content? For example these two queries:

SELECT “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC

SELECT “Middlename” || ’ ’ || “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC

Returning a list like this:
Surname, Forename
Surname, Forename
Middlename Surname, Forename
Surname, Forename
Middlename Surname, Forename
Middlename Surname, Forename

[RobertG]

@jdsaljdsajdl

SELECT “Middlename” || ’ ’ || “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC

Do it this way:

SELECT COALESCE("Middlename" || ' ','') || "Surname" || ', ' || "Forename" AS "Name", 
"ID" FROM "People" ORDER BY "Name" ASC

You could use IFNULL instead of COALESCE with internal HSQLDB, not with internal Firebird. I would prefer to sort by the complete “Name”, because it would sort starting with first character of the enries.

[jdsaljdsajdl]

This worked wonderfully! Thank you!