CODEWITHSUNDEEP
sqlpostgresql
anegru
anegru

Reputation: 1123

How to join SQL tables and include missing rows?

Tables

Suppose you have a table, meal_types, as follows:

meal_type_id  |  type
--------------|------------
     1        |  breakfast
     2        |  lunch
     3        |  dinner

The second table is called meals:

meal_id  |  food    |  meal_type_id
---------|----------|------
   1     |  bacon   |   1
   2     |  eggs    |   1
   3     |  fries   |   2
   4     |  burger  |   2
   5     |  soup    |   3

The third table is called meal_counts:

external_id | count | meal_id
------------|-------|---------
    1       |  100  |   3
    2       |  50   |   5
    2       |  25   |   3
    3       |  15   |   2

Expected output

Return the total counts per external_id grouped by meal_type_id. All meal_type_id values must appear for every external_id. If an external_id does not contain a meal_type_id its count must be set to 0.

 external_id | count | meal_type_id
 ------------|-------|-------------
     1       |  0    |   1
     1       |  100  |   2
     1       |  0    |   3
     2       |  0    |   1
     2       |  25   |   2
     2       |  50   |   3
     3       |  15   |   1
     3       |  0    |   2
     3       |  0    |   3

Please note that even though for external_id = 1 there are no counts for meals of type breakfast(1) and lunch(2), they are still added to the result and their count is set to 0.

My work on this

SELECT external_id, SUM(count) as total, meal_type_id
FROM meal_counts
INNER JOIN meals ON meal_counts.meal_id = meals.meal_id
INNER JOIN meal_types ON meal_types.meal_type_id = meals.meal_type_id
GROUP BY external_id, meal_type_id

The issue with this approach is that if an external_id does not contain a meal_type_id, it will not return all meal_types.

How can I solve this with one query?

Thank you.

Upvotes: 0

Views: 100

Answers (2)

eshirvana
eshirvana

Reputation: 24568

you can use cross join, which gives you all possible combination of meal_type in meal_counts table:

select 
   external_id 
  ,mt.meal_type_id 
  ,coalesce(max(counter) filter (where mt.meal_type_id = m.meal_type_id),0) counter
from meal_counts mc
join meals m
  on m.meal_id = mc.meal_id
cross join meal_types mt 
group by external_id, mt.meal_type_id
order by external_id, mt.meal_type_id

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

If I understand this correctly, you can use a derived table getting the external_ids, cross join it with the meal_types, left join it with the meal_counts and use coalesce() to get the count of 0 where no join partner providing the count was found.

SELECT mc2.external_id,
       coalesce(mc3.count, 0) count,
       mt1.meal_type_id
       FROM (SELECT DISTINCT
                    mc1.external_id
                    FROM meal_counts mc1) mc2
            CROSS JOIN meal_types mt1
            LEFT JOIN meal_counts mc3
                      ON mc3.external_id = mc2.external_id;

Upvotes: 1

Related Questions