SQL 查询 - 我哪里出错了？

Question

我有两张 table ，一张用于开业，另一张用于预订。预订表中的条目总是指一个空缺，每个空缺可能有多个预订。我想提取所有预订与 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

以下是我尝试过的，但它不正确，因为它在示例 1 中失败了:

select op.id, bo.id 
  from opening op
  left join booking bo on bo.openingId = op.id
  where bo.bookingType != 'C';

以下是关于时间间隔的完整查询:

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