tags:

views:

235

answers:

4

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.

A: 
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'
Cătălin Pitiș
-1 The subquery doesn't do anything here?
Andomar
Actually... it does a left outer join. And the main query filters the result of the left outer join...
Cătălin Pitiș
But a left outer join and where can be done without a subquery?
Andomar
+7  A: 
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')
     )
akf
In theory this was what I was trying to avoid... a subquery sounds a bit heavy, but it works
Consider the left join syntax solution from aaron as well. A left join is often faster than a not in subquery.
HLGEM
A good SQL database will usually optimize a subquery into a left join so it has the same performance. Write the statement with a subquery - it's eaasier to code and understand.
Paul Morgan
+2  A: 

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'
)
Andomar
+1  A: 

@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
Aaron Alton