SQL連接三個表並顯示值為零的列

我試圖顯示事件價格 = 0 的所有城市名稱及其事件計數

create table event
(eventID decimal(12) not null primary key, 
AddressID decimal(12) not null, 
eventName varchar(255) not null, 
Description varchar(255) not null, 
eventPrice decimal(10,2) not null,
eventDate date,
foreign key (addressID) references address(addressID));


create table city (
cityID decimal(12) not null primary key, 
cityName varchar(30) not null);

create table address ( 
addressID decimal(12) not null primary key, 
address varchar(100) not null, 
cityID decimal(12) not null, 
zipcode varchar(5)
foreign key (cityID) references city(CityID) );



insert into address values (103, '310 East Kingsbridge Road',204,'10458');
insert into city values (200,'Manhattan');
insert into City values (201,'Brooklyn');
insert into City values (202,'Queens');
insert into City values (203,'Staten Island');
insert into City values (204,'Bronx');

insert into event values
(3001,103,'Book Fair','Book fair is dedicated to engaging and growing the community of poets and writers',0,'2-may-2020');

下面的選擇給了我以下輸出

select  cityName, count (eventPrice) as numberOfFreeEvents
from city
join address on address.cityId = city.cityID
left join event on event.addressID= address.addressID
group by cityName, eventPrice
having eventPrice = 0

輸出：

cityName    |    numberOfFreeEvents

    Bronx  |    1

我試圖讓輸出成為

cityName           |numberOfFreeEvents

Bronx                   1

Manhattan               0

Staten Island           0

Queens                  0

Brooklyn                0

您需要 FROM 子句中的 EventPrice 條件，因為它將過濾掉 WHERE 或 HAVING 中的行。

試試這個：

SELECT 
cityName,
COUNT(eventPrice) AS numberOfFreeEvents
FROM 
city
JOIN address
ON address.cityId = city.cityID
LEFT JOIN event
ON event.addressID = address.addressID
AND eventPrice = 0

GROUP BY 
cityName,
eventPrice

引用自：https://dba.stackexchange.com/questions/265700