I've got two tables, one for openings and the other for bookings. An entry in the bookings table always refers to an opening and there may be multiple bookings for each opening. I would like to extract all the openings that have bookings different from bookingType 'C'
.
E.g.
if an opening has 3 bookings of type A, B and C, it should NOT show up in the result
if an opening has only bookings of type A and B, it should show up in the result
The following is what I've tried but it is not correct as it fails for the example 1:
select op.id, bo.id
from opening op
left join booking bo on bo.openingId = op.id
where bo.bookingType != 'C';
Here is the complete query referring to time intervals:
select op.id, bo.id
from opening op
left join booking bo on bo.openingId = op.id
where ((bo.arrivalDate < '2009/06/20' AND bo.departureDate <= '2009/06/20') OR
(bo.arrivalDate >= '2009/06/27' AND bo.departureDate > '2009/06/27'))
What I used to call bookingType
was actually a time interval defined through the two columns arrivalDate
and departureDate
: in the example above I need all the openings that DO NOT have a booking between the 20th June 2009
and the 27th June 2009
.