title: left join to two inner joined tables
body: <p>I'm sure i'm probably just being forgetful or overcomplicating... but here's my scenario:</p>
  
  <h2>Calendar table (record for every day of every year)</h2>
  
  <pre><code>iso_date
  01/01/01
  02/01/01
  03/01/01
  04/01/01
  </code></pre>
  
  <h2>Venues table (record for every venue)</h2>
  
  <pre><code>venue
  All London
  London Tower
  London Bridge
  Millenium Bridge
  </code></pre>
  
  <h2>Composite venues table (reference to linked venues)</h2>
  
  <pre><code>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
  </code></pre>
  
  <h2>Bookings table (record for every booking including date and venue)</h2>
  
  <pre><code>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
  </code></pre>
  
  <h2>Events table</h2>
  
  <pre><code>event, status
  1,     1
  2,     0
  3,     1
  4,     1
  </code></pre>
  
  <p>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.</p>
  
  <h2>Output</h2>
  
  <pre><code>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
  </code></pre>
  
  <p>I can't use the event status in the where clause as it will remove the record entirely. </p>
  
  <p>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?</p>