SQL Query - Where am I going wrong?

Question

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.

1. if an opening has 3 bookings of type A, B and C, it should NOT show up in the result

2. 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.

Answer 1

select opid, boid from 
   (select op.id opid, bo.id boid, bo.bookingType bookingType 
       from 
       openings op left outer join bookings bo on op.id = bo.id
   )
where bookingType <> 'C'

Answer 2

SELECT op.id 
FROM opening op 
WHERE op.id NOT IN 
     (SELECT b.openingid 
      FROM booking b 
      WHERE b.bookingtype='C')

with the date change:

SELECT op.id 
FROM opening op 
WHERE op.id NOT IN 
     (SELECT b.openingid 
      FROM booking b 
      WHERE (b.arrivalDate BETWEEN '2009/06/20' AND '2009/06/27') 
             OR 
            (b.departureDate BETWEEN  '2009/06/20' and '2009/06/27')
     )

Answer 3

Here's an easy version without joins, you don't even need the Openings table:

select openingId, id
from booking
where openingId not in (
    select openingId
    from booking
    where bookingType = 'C'
)

Answer 4

@Frankie - No need for a NOT IN clause. You can use a Left-Anti-Semi Join as well - like this:

SELECT op.id 
FROM opening op 
LEFT OUTER JOIN booking b ON op.id = b.openingid AND b.bookingtype = 'C'
WHERE b.OpeningID IS NULL

and this:

SELECT op.id 
FROM opening op 
LEFT OUTER JOIN booking b ON op.id = b.OpeningID
 AND b.ArrivalDate BETWEEN '2009/06/20' AND '2009/06/27'  
 AND b.DepartureDate BETWEEN '2009/06/20' AND '2009/06/27'
WHERE b.OpeningID IS NULL