SQL Server How to Include zero-count when joining 3 tables?

6yoyoihd 于 2023-05-16 发布在其他

关注(0)|答案(2)|浏览(83)

I have 3 tables: Vehicle, type and color

Vehicle

id  brand   type_id color_id
1   Toyota     1       2
2   Toyota     2       3
3   GMC        2       1
4   BMW        2       1

Type

id  name
1   Truck
2   Car

Color

id  name
1   White
2   Red
3   Black

I want to get the count of each combination of type and color including 0 values, like this:

type.name   color.name          count
Truck         White               0
Truck          Red                1
Truck         Black               0
Car           White               2
Car            Red                0
Car           Black               1

I tried this query below

SELECT type.name, color.name, count(vehicle.id) count
FROM vehicle
RIGHT JOIN type on vehicle.type_id = type.id
RIGHT JOIN color on color.id = vehicle.color_id
GROUP BY type.name, color.name

but it doesn't produce zero values:

type.name   color.name          count
Truck          Red                1
Car           White               2
Car           Black               1

Is there a neat query to achieve the first results?

sql-server

来源：https://stackoverflow.com/questions/76226820/how-to-include-zero-count-when-joining-3-tables

2条答案

按热度按时间

2skhul331#

As mention @jarlh in comments, this can be done using CROSS JOIN :

WITH CTE AS (
  SELECT t.id as type_id, t.name as type_name, c.id as color_id, c.name as color_name
  FROM type t
  CROSS JOIN color c
)
SELECT c.type_name, c.color_name, count(t.color_id) as count
FROM CTE c
LEFT JOIN vehicle t on t.type_id = c.type_id and t.color_id = c.color_id
GROUP BY c.type_name, c.color_name
order by c.type_name desc, c.color_name desc

Or simply :

SELECT t.name as type_name, c.name as color_name, count(v.id) as count
FROM type t
CROSS JOIN color c
LEFT JOIN vehicle v on v.type_id = t.id and v.color_id = c.id
GROUP BY t.name, c.name
order by t.name desc, c.name desc

Results :

type_name   color_name  count
Truck       White       0
Truck       Red         1
Truck       Black       0
Car         White       2
Car         Red         0 
Car         Black       1

Demo here

赞(0）回复(0）举报 2023-05-16

2g32fytz2#

Here's another way of doing it.

with record_count as (

select
b.id [typeid],
b.name [typename],
c.id [colorid],
c.name,
[colorname],
count(*) record_count
from vehicle a
left join type b on a.type_id = b.id
left join color c on a.color_id = c.id
group by b.id, b.name, c.id, c.name

)

select a.typename, a.colorname, isnull(b.record_count,0) count
from (

select distinct type.id [typeid], type.name [typename], color.id
[colorid], color.name [colorname]
from vehicle , type, color

) as a

left join record_count b on a.typeid = b.typeid
and a.colorid = b.colorid

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server How to Include zero-count when joining 3 tables?

2条答案

相关问题

热门标签

最新问答