title: Cartesian join with multple outer joins to common root
body: <p>I have the following schema. </p>
  
  <p><img src="https://i.stack.imgur.com/iK8DJ.png" alt="Schema Picture"></p>
  
  <p>I can run two queries fairly simply </p>
  
  <pre><code>select *  from booking_model_assignment 
       join booking_model on booking_model_assignment.booking_model_id = booking_model.id
  left outer join axis_channel_mappings on bmi_id = axis_channel_mappings.assignment_id
  left outer join axis_revenue_stream_mappings on bmi_id = axis_revenue_stream_mappings.assignment_id
  </code></pre>
  
  <p>which will give me all of the combinations of <code>channel mappings</code> and 'revenue_stream_mappings' which fit a booking model, with Null if there is one which only matches in one of the tables.</p>
  
  <p>The other query</p>
  
  <pre><code>select * from axis_channel join axis_revenue_stream
  </code></pre>
  
  <p>Gives all of the possible combinations of channels and revenue streams. </p>
  
  <p>What I would like is a query which will give all of the combinations, and the booking_model if that combination matches. </p>
  
  <p>Any time I try to join or subquery I seem to get too many, or too few results. I think the issue is that I want the assignment_id to match across outer joins but only if there is an outer join. </p>
  
  <p>The schema is laid out like this so it will be possible to add new axis and fit models to combinations, so if there is an easier way to achieve this I would be open to changing the schema.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have a partial solution based on Eggyal's answer but it is not extendable. </p>
  
  <pre><code>SELECT       c.*, r.*, GROUP_CONCAT(a.bmi_id), GROUP_CONCAT(b.name) AS booking_models
  FROM         axis_channel                 c
  CROSS JOIN axis_revenue_stream          r
  LEFT JOIN axis_channel_mappings        cm ON cm.channel_id        = c.id
  LEFT JOIN axis_revenue_stream_mappings rm ON rm.revenue_stream_id = r.id
  LEFT JOIN booking_model_assignment     a  ON (a.bmi_id = cm.assignment_id
                                               AND a.bmi_id = rm.assignment_id)
                                               OR (a.bmi_id = cm.assignment_id
                                               AND rm.assignment_id IS NULL)
                                               OR (cm.assignment_id  IS NULL
                                               AND a.bmi_id = cm.assignment_id)
  LEFT JOIN booking_model                b  ON b.id = a.booking_model_id
  GROUP BY     c.id, r.id
  </code></pre>
  
  <p>But if I were to add more axes this query would grow way to cumbersome. </p>