SQL查询-我哪里出错了？

Question

我有两张桌子，一张是空位的，另一张是预订的。“预订”表中的条目总是指一个开口，每个开口可能有多个预订。我想提取所有的空缺，有不同的预订与bookingType 'C'。

等。

1. 如果一个开口有3个A、B和C类型的预订，如果一个开口只有A和B类型的预订，则它不应该出现在结果
2. 中，它应该显示在结果

中。

下面是我尝试过的内容，但不正确，因为示例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')
     )

Answer 2

这里有一个没有联接的简单版本，您甚至不需要打开表：

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

Answer 3

@Frankie -不需要使用非IN条款。您也可以使用左反半连接，例如：

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

这是：

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