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

Question

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

please tag your SQL engine and provide sample data in tabular form. — zealous, Jul 05 '20 at 06:31
Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — Muhammad Vakili, Jul 05 '20 at 06:32
Your second query's `CASE` expression is redundant because the `WHERE` clause ensures all `status` values will be `'Online'`. Please explain what your second query is actually meant to accomplish. — Dai, Jul 05 '20 at 06:36
Your second query is invalid: the `status` value is not included in the `GROUP BY` clause nor is it part of an aggregate operation. Why does your second query have a `GROUP BY` clause but performs no aggregate operation at all? — Dai, Jul 05 '20 at 06:38
`Objects` is the key `table1` and `table2`. Btw your solution worked. Thanks — user630702, Jul 05 '20 at 07:20

zealous · Answer 1 · 2020-07-05T06:36:25.157

0

Try the following using left join table1 and table2 on Objects.

select 
    t1.Objects, 
    coalesce(COUNT (t1.*), 0) as  Objects_count,
    case 
        when t2.status = 1 then 'Online'
        else 'Offline'
    end as Status 
from table1 t1
left join table2 t2
on t1.Objects = t2.Objects
GROUP BY 
    Objects,  
    case 
        when t2.status = 1 then 'Online'
        else 'Offline'
    end

edited Jul 05 '20 at 06:36

answered Jul 05 '20 at 06:30

zealous

7,336
4
16
36

score 0 · Accepted Answer · answered Jul 05 '20 at 06:35

0

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

answered Jul 05 '20 at 06:35

Dai

141,631
28
261
374

@User . . . Based on your question `full join` is overkill and not needed for this query. – Gordon Linoff Jul 05 '20 at 12:33
@GordonLinoff When I wrote my answer I wasn't sure if `table1` and `table2` represented distinct subsets of all of the data or not. – Dai Jul 05 '20 at 12:46
. . The comment was directed at the OP, not at you. This is a fine approach if needed, but nothing in the question really suggests that it is needed -- there are no `0`s in the sample results, for instance. – Gordon Linoff Jul 05 '20 at 16:56

score 0 · Answer 3 · answered Jul 05 '20 at 12:28

0

I would use a correlated subquery:

SELECT t1.Objects, COUNT(*) AS Objects_count,
       (CASE WHEN EXISTS (SELECT 1
                          FROM table2 t2
                          WHERE t2.Objects = t1.Objects AND t2.status = 1
                         )
             THEN 'Online' ELSE 'Offline'
        END) as status
FROM table1 t1
GROUP BY t1.Objects

This returns all rows from table1 along with the status.

Alternatively, if table2 has no duplicate rows, you can use a simple LEFT JOIN and aggregation:

SELECT t1.Objects, COUNT(*) AS Objects_count,
       (CASE WHEN COUNT(t2.Objects) > 0 THEN 'Online' ELSE 'Offline' END) as status
FROM table1 t1 LEFT JOIN
     table2 t2
     ON t2.Objects = t1.Objects AND t2.status = 1
GROUP BY t1.Objects;

answered Jul 05 '20 at 12:28

Gordon Linoff

1,242,037
58
646
786

Updated my question with left joins. I want to test your query but I'm unsure how to use alias when there are other left joins. Can you update your answer? – user630702 Jul 05 '20 at 17:03
@user . . . (1) Don't update a question after it has been answered, particularly in a way that invalidates answers. That draws downvotes on that answers. (2) You may have syntactically put `left join` into the answer, but the `where` clause just turns it into an inner join. You should probably ask a new question with appropriate sample data, desired results and explanation. – Gordon Linoff Jul 05 '20 at 17:15
Okay I'll revert the question back to its original state. – user630702 Jul 05 '20 at 17:24
Can you update your answer to fit a couple of left joins and then alias? I think a new question asking on how to make it look like your query might bring downvotes. – user630702 Jul 05 '20 at 17:25

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

Example:

Second query:

3 Answers3