sql - 左连接到两个内部连接的表

Question

我确定我可能只是健忘或过于复杂......但这是我的场景:

日历表(记录每一年的每一天)

iso_date
01/01/01
02/01/01
03/01/01
04/01/01

场馆表(每个场馆的记录)

venue
All London
London Tower
London Bridge
Millenium Bridge

复合 field 表(引用链接 field )

master_venue,     child_venue
All London,       All London
All London,       London Tower
All London,       London Bridge
All London,       Millenium Bridge
London Tower,     London Tower
London Bridge,    London Bridge
Millenium Bridge, Millenium Bridge

预订表(每次预订的记录，包括日期和地点)

iso_date, venue,            event
01/01/01, All London,       1
02/01/01, London Tower,     2
02/01/01, Millenium Bridge, 3
04/01/01, London Bridge,    4

事件表

event, status
1,     1
2,     0
3,     1
4,     1

现在我想加入表格，这样我每天都能得到每个 field 的记录，无论它是否已被预订。如果 field 有预订，我只想在事件状态为 1 时看到它。

输出

iso_date, venue,            booked
01/01/01, All London,       1
01/01/01, London Tower,     1
01/01/01, London Bridge,    1
01/01/01, Millenium Bridge, 1
02/01/01, All London,       0
02/01/01, London Tower,     0
02/01/01, London Bridge,    0
02/01/01, Millenium Bridge, 1
03/01/01, All London,       0
03/01/01, London Tower,     0
03/01/01, London Bridge,    0
03/01/01, Millenium Bridge, 0
04/01/01, All London,       0
04/01/01, London Tower,     0
04/01/01, London Bridge,    1
04/01/01, Millenium Bridge, 0

我不能在 where 子句中使用事件状态，因为它会完全删除记录。

我知道我可以使用子查询或一些复杂的 case 语句，但我是否可以智能地连接表来解决我的问题？

Answer 1

你应该能够写:

SELECT Calendar.iso_date AS iso_date,
       Venues.venue AS venue,
       COALESCE(Events.status, 0) AS booked
  FROM Calendar
 CROSS
  JOIN Venues
  LEFT
 OUTER
  JOIN (      Bookings
         JOIN Events
           ON Events.event = Bookings.event
          AND Events.status = 1
       )
    ON Bookings.iso_date = Calendar.iso_date
   AND Bookings.venue = Venues.venue
;

(免责声明:未经测试。)