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