Reputation: 354
I need some help with an SQL query.
Let's say we have this sample database which manages Bookings in a hotel:
Customer(ID, name, birthDate, city);
Room(number, floor, beds, roomtype, price);
Bookings(Customer, roomNumber, floor, checkInDate, checkOutDate);
I need to know which customers booked only and ONLY economic type of rooms. This is my query:
select Customer from Bookings
join Room on(Bookings.num = camera.roomNumber and Bookings.floor=
Room.floor)
where (Bookings.Customer, Bookings.floor) not in (select number, floor from
Room where roomType != 'economic')
My issue is that this query shows me customers which booked economic rooms, but it also shows me customers which booked other type of rooms. How can I restrict the output in order to get Customers which booked ONLY economic rooms? Thank you
Upvotes: 0
Views: 53
Reputation: 50173
Use not exists
:
select c.*
from Customer c
where not exists (select 1
from Bookings b
inner join Room r on b.num = r.roomNumber and b.floor = r.floor
where c.ID = b.Customer and r.roomType <> 'economic'
);
Upvotes: 2
Reputation: 1270633
You can use aggregation:
select b.Customer
from Bookings b join
Room r
on b.num = r.roomNumber and b.floor = r.floor
group by b.Customer
having min(roomType) = max(roomType) and min(roomType) = 'economic';
Upvotes: 2