Mysql - where in all?

Question

I know this is simple but strangely my search did not show any satisfactory answers.

Let's say I have tables "hotel", "guest", and "booking".

Hotel contains (hotel#, city)

Guest contains (guest#, guest_name)

Booking contains (hotel#, guest#, book_date)

If I were to find all guests who have booked in EVERY hotels in London, how should I write my queries?

I have tried so far as to create view that contains all of hotels located in London and tried "where in all" queries with that but none seems to give me answers...any help would be much appreciated.

----- Here are my actual tables

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    |       |
+--------+-------------+------+-----+---------+-------+

Answer 1

Try this:

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"
);

If you could provide a SQL fiddle for this we could play around it.

Answer 2

You can try it with not exists like this:

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#))