How to join SQL tables and include missing rows?

Question

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.

eshirvana · Accepted Answer · 2021-04-02 23:08:24Z

1

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

edited Apr 2, 2021 at 23:08

answered Apr 2, 2021 at 22:23

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sticky bit · Accepted Answer · 2021-04-02 22:26:29Z

1

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;

answered Apr 2, 2021 at 22:26

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to join SQL tables and include missing rows?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related