MySQL查询，三个表，外部连接

Question

编辑:回答：

SELECT c.Name FROM Country c
WHERE NOT EXISTS 
( SELECT * FROM City 
JOIN Building ON City.CityID = Building.CityID 
WHERE City.CountryID = c.CountryID);

谢谢你们的时间伙计们。

我有这样的模式：

​

​

我这样做是为了选择所有没有建筑的城市。

select * from City c 
  left join Building b on b.CityID = c.CityID
  where b.CityID is null or c.CityID is null
  union all
  select * from City c 
  right join Building b on b.CityID = c.CityID 
  where b.CityID is null or c.CityID is null;

如何选择没有建筑物的国家名称？

表说明：

​

1. 数据示例：

​

​

2.预期产出:英格兰

Answer 1

试试这个，告诉我

 select * from country where CountryID not in
 (select CountryID from country as c   join city as t on c.CountryID =t.CountryID join building as b on t.CityID = b.CityID )

Answer 2

请考虑以下几点：

SELECT o.countryid
     , o.name country
     , i.cityID
     , i.name city
     , b.buildingid 
  FROM country o 
  LEFT 
  JOIN city i 
    ON i.countryid = o.countryid 
  LEFT 
  JOIN building b 
    ON b.cityid = i.cityid;
+-----------+---------+--------+-----------+------------+
| countryid | country | cityID | city      | buildingid |
+-----------+---------+--------+-----------+------------+
|         2 | England |      2 | London    |       NULL |
|         2 | England |      5 | Liverpool |       NULL |
|         4 | France  |      4 | Paris     |          5 |
|         3 | Germany |      3 | Berlin    |          3 |
|         3 | Germany |      3 | Berlin    |          4 |
|         1 | Poland  |      1 | Warsaw    |          1 |
|         1 | Poland  |      1 | Warsaw    |          2 |
+-----------+---------+--------+-----------+------------+

这个问题的最后一部分是留给读者的练习。

Answer 3

SELECT Country.Name
FROM Country
JOIN City ON (Country.CountryID = City.CountryID)
WHERE City.CityID =
(SELECT City.CityID FROM City
JOIN Building
ON (City.CityID = Building.CityID)
WHERE City.CityID NOT IN Building.CityID)