Sergio
Sergio

Reputation: 354

How to get count of customers?


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

Answers (2)

Yogesh Sharma
Yogesh Sharma

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

Gordon Linoff
Gordon Linoff

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

Related Questions