Skip to content
Advertisement

SQL – Join two queries and also set a default value when there is no match

Is it possible to merge the following two queries and also have a default value offline for status when it doesn’t have entry to match?

Example:

SELECT
    Objects,
    COUNT(*) AS Objects_count
FROM
    table1
GROUP BY
    Objects
---------------------------------
Objects         |   Objects_count
---------------------------------
Unitied_states  |   2           
Germany         |   2           
Turkey          |   1           
Kuwait          |   2   

Second query:

Note: when the status is not equal to 1, there is no entry for that in table2. So in table two we have only entries that are online.

SELECT
    Objects, 
    CASE 
        WHEN status = 1 THEN 'ONLINE'
    END AS Status
FROM
    table2
WHERE
    status = 1
GROUP BY
    Objects
--------------------------
Objects         |   Status
--------------------------
Unitied_states  |   Online          
Turkey          |   Online  

I want the final output to be something like

-------------------------------------------
Objects         |   Objects_count  | Status
-------------------------------------------
Unitied_states  |   2              | Online
Germany         |   2              | Offline
Turkey          |   1              | Online
Kuwait          |   2              | Offline

Advertisement

Answer

Yes, this is possible, by doing a FULL OUTER JOIN.

I also assume you want to JOIN on the Objects column:

SELECT
    COALESCE( t1.Objects, t2.Objects ) AS Objects,
    COALESCE( t1.Objects_Count, 0 ) AS Objects_Count,
--  t2.Status,
    CASE t2.status WHEN 1 THEN 'Online' ELSE 'Offline' END AS status
FROM
    (
        SELECT
            Objects,
            COUNT(*) AS Objects_count
        FROM
            table1
        GROUP BY
            Objects

    ) AS t1

    FULL OUTER JOIN
    (
        SELECT
            Objects, 
            status
        FROM
            table2

    ) AS t2 ON t1.Objects = t2.Objects
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement