SQL Query - Where Am I Wrong?

Question

I have two tables, one for openings and one for orders. The entry in the reservation table always refers to the opening, and for each opening there can be several orders. I would like to extract all holes that have orders other than bookingType 'C'.

eg.

• if the opening has 3 orders of type A, B and C, it should NOT be displayed as a result

• if the opening has only orders of type A and B, it should be displayed in Result

The following is what I tried, but this is not true as it is not suitable for 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 timestamp query:

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

, bookingType, , arrivalDate departureDate: , 20th June 2009 27th June 2009.

Answer 1

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

:

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 2

, Openings:

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

Answer 3

@Frankie - no need for a NOT IN clause. You can also use Left-Anti-Semi Join - 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

Answer 4

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'