我知道这很简单,但奇怪的是我的搜索没有显示任何令人满意的答案。
假设我有"酒店"、"客人"和"预订"表格。
酒店包含(酒店#,城市(
来宾包含(来宾#、guest_name(
预订包含(酒店#、客人#、book_date(
如果我要找到所有在伦敦每家酒店预订过的客人,我应该如何写我的查询?
到目前为止,我已经尝试创建包含位于伦敦的所有酒店的视图,并尝试了"全部在哪里"查询,但似乎没有人给我答案......任何帮助将不胜感激。
----- 这是我的实际表格
Hotel
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| hotel# | int(11) | NO | PRI | 0 | |
| hotel_name | varchar(20) | YES | | NULL | |
| city | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
Guest
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| guest# | int(11) | NO | PRI | 0 | |
| guest_name | varchar(15) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| guest_city | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
Booking
+-----------+-------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+------------+-------+
| hotel# | int(11) | NO | PRI | 0 | |
| guest# | int(11) | NO | PRI | 0 | |
| date_from | date | NO | PRI | 0000-00-00 | |
| date_to | date | YES | | NULL | |
| room# | smallint(6) | YES | | NULL | |
+-----------+-------------+------+-----+------------+-------+
Room
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| room# | int(11) | NO | PRI | 0 | |
| hotel# | int(11) | NO | PRI | 0 | |
| type | varchar(15) | YES | | NULL | |
| price | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
你可以像这样尝试不存在:
select a.guest#,a.guest_name
from guest a
where not exists (select b.hotel#
from hotel b
where b.city='London' and not exists
(select *
from booking c
where c.guest#=a.guest# and c.hotel#=b.hotel#))
试试这个:
select distinct(g.guest#) from booking b join guest g on g.guest#=b.guest# where
b.hotel# in (select distinct hotel# from hotel where hotel_city="LONDON")
having count(distinct b.hotel#)=(
select count(distinct hotel#) from hotel h where h.city="LONDON"
);
如果你能为此提供一个SQL小提琴,我们可以绕过它。