CODEWITHSUNDEEP
sqlsql-server
Lee Tickett
Lee Tickett

Reputation: 6027

left join to two inner joined tables

I'm sure i'm probably just being forgetful or overcomplicating... but here's my scenario:

Calendar table (record for every day of every year)

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

Venues table (record for every venue)

venue
All London
London Tower
London Bridge
Millenium Bridge

Composite venues table (reference to linked venues)

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

Bookings table (record for every booking including date and venue)

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

Events table

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

Now I want to join the tables such that i get a record for every venue for every day regardless of whether it has been booked. Where a venue has a booking i only want to see it if the event status is 1.

Output

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

I can't use the event status in the where clause as it will remove the record entirely.

I know I could use a subquery or some complex case statements, but is it possible i can intelligently join the tables to solve my problem?

Upvotes: 3

Views: 3635

Answers (3)

ruakh
ruakh

Reputation: 183290

You should be able to write:

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
;

(Disclaimer: not tested.)

Upvotes: 6

dan1111
dan1111

Reputation: 6566

select iso_date, 
       venue,
       max(case when events.event is not null then 1 else 0 end) as booked
    from calendar
    cross join venue
    left outer join bookings on
        bookings.date  = iso_date and
        bookings.venue = venue.venue
    left outer join events on 
        events.status = 1 and 
        events.event = bookings.event
    group by iso_date, venue

Update: rewrote the query with two outer joins so that it would be efficient even if only getting a small subset of the records.

Here is how you would most efficiently get a single date and venue:

select iso_date, 
       venue,
       max(case when events.event is not null then 1 else 0 end) as booked
    from calendar
    inner join venue on
        calendar.iso_date = '01/01/01' and
        venue.venue = 'my front lawn'
    left outer join bookings on
        bookings.date  = iso_date and
        bookings.venue = venue.venue
    left outer join events on 
        events.status = 1 and 
        events.event = bookings.event
    group by iso_date, venue

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269713

You need to start with a driving table, which is the cross join of calendar and venues. Then, join in the bookings to get the booking information:

select c.iso_date, v.venue,
       (case when b.event is NULL then 0 else 1 end) as Booked
from calendar c cross join
     venues v left outer join
     bookings b
     on b.iso_date = c.iso_date and
        b.venue = v.venue

Upvotes: 0

Related Questions