In PostgreSQL, I have a table with some settings the user can make. In that table there is also a "global" user that takes place if the user doesn’t have any settings for them.
This query returns the settings for the user:
SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
FROM SETTINGS
WHERE FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE
This one returns the settings (only the XML actually) for the "global" user:
SELECT FIELD_XML AS GLOBAL_XML
FROM SETTINGS
WHERE FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE
What I want is a query that returns a single row with FIELD_1, FIELD_2, FIELD_3, FIELD_XML, GLOBAL_XML
.
The problem is that either of these queries could return null, so my attempt at using a subquery fails:
SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML,
(SELECT FIELD_XML
FROM SETTINGS
WHERE FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE) AS GLOBAL_XML
FROM SETTINGS
--Fails if the user doesn't have a setting. Swapping fails if there is no global user.
WHERE FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE
The only way I managed to get the result I wanted was to use a FULL JOIN
with subqueries:
SELECT A.FIELD_1, A.FIELD_2, A.FIELD_3, A.FIELD_XML, B.FIELD_XML AS GLOBAL_XML
FROM (SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
FROM SETTINGS
WHERE FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE) A
FULL JOIN
(SELECT FIELD_XML
FROM SETTINGS
WHERE FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE) B ON B.FIELD_TYPE = A.FIELD_TYPE
This returns me FIELD_1, FIELD_2, FIELD_3, FIELD_XML
(possibly being null) and GLOBAL_XML
(also possibly being null). But is there a better way to do this?
2
Answers
Select both
User
andGlobal
and take 1If we can assume each query only returns zero or one record use a cross join.
Not sure if this is cleaner/more elegant… I generally find this easier to read/ maintain; but others may not.
Basically write both queries as a CTE then cross join.