JOIN multiple tables based on one reference table and count records

Question

I have these following tables:

cars:

id   manufacturer
--------------------        
1     Nissan
2     Toyota 
3     Honda
4     Volkswagen

type:

id       type                    car_id
--------------------------------------------
1        maxima                   1
2        civic                    3
3        accord                   3
4        corolla                  2
5        altima                   1

color:

id      color          car_id
———————————--------------------
1       yellow          1
2       blue            2
3       blue            1
4       black           4
5       red             1

desired table:

car_id       total_type       total_colors
————————————————---------------------------
1                2                     3               
2                1                     1
3                2                     0
4                0                     1

How could I get the resulting table? I would prefer not to use the with clause. Would a CROSS JOIN be the optimal way?

Answer 1

It seems it could be done with 2 LEFT JOINs and COUNT DISTINCT aggregate functions. Something like this

select c.id, count(distinct t.id) total_type, count(distinct co.id) total_colors, 
from cars c
     left join [types] t on c.id=t.car_id
     left join colors co on c.id=co.car_id
group by c.id
order by 1;