skip to Main Content

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


  1. Select both User and Global and take 1

    select * from
    (
      SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
             ,'1' ordN,'User' src
      FROM   SETTINGS
      WHERE  FIELD_USR = :MY_USR AND FIELD_TYPE = :FIELD_TYPE
      union all
      SELECT  FIELD_1, FIELD_2, FIELD_3, FIELD_XML
             ,'1' ordN,'Global' src
      FROM   SETTINGS
      WHERE  FIELD_USR = :GLOBAL_USR AND FIELD_TYPE = :FIELD_TYPE
    ) x
    order by ordN
    limit 1
    
    Login or Signup to reply.
  2. If 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.

    WITH 
    S1 as (
      SELECT FIELD_1, FIELD_2, FIELD_3, FIELD_XML
        FROM SETTINGS S1
       WHERE S1.FIELD_USR = :MY_USR 
         AND S1.FIELD_TYPE = :FIELD_TYPE),
    
    S2 as (SELECT FIELD_XML
             FROM SETTINGS
            WHERE FIELD_USR = :GLOBAL_USR 
              AND FIELD_TYPE = :FIELD_TYPE)
    
    SELECT S1.FIELD_1, S1.FIELD_2, S1.FIELD_3, S1.FIELD_XML, S2.FIELD_XML  AS GLOBAL_XML
    FROM S1
    CROSS JOIN S2
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search