SQL查询 - 我哪里错了？

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

我有两张桌子，一张用于开放，另一张用于预订。预订表中的条目始终指的是开口，每个开口可能有多个预订。我想提取所有预订不同于bookingType'C'的空缺。

E.g.

例如。

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

如果一个开头有3个A，B和C类型的预订，它不应该出现在结果中

if an opening has only bookings of type A and B, it should show up in the result

如果开口只有A和B类型的预订，它应该显示在结果中

The following is what I've tried but it is not correct as it fails for the example 1:

以下是我尝试过的但是它不正确，因为它失败了示例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.

我以前称之为bookingType实际上是通过两列arrivalDate和departureDate定义的时间间隔：在上面的示例中，我需要在2009年6月20日到2009年6月27日之间没有预订的所有空缺。

4 个解决方案

#1

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

#2

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

这是一个没有连接的简单版本，你甚至不需要Opennings表：

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

#3

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

@Frankie - 不需要NOT IN子句。您也可以使用Left-Anti-Semi Join - 如下所示：

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

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

#1

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