0

I have the following schema.

Schema Picture

I can run two queries fairly simply 

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

which will give me all of the combinations of channel mappings and 'revenue_stream_mappings' which fit a booking model, with Null if there is one which only matches in one of the tables.

The other query

select * from axis_channel join axis_revenue_stream

Gives all of the possible combinations of channels and revenue streams.

What I would like is a query which will give all of the combinations, and the booking_model if that combination matches.

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.

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.

EDIT

I have a partial solution based on Eggyal's answer but it is not extendable. 

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

But if I were to add more axes this query would grow way to cumbersome.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeremy French
  • 11,707
  • 6
  • 46
  • 71

1 Answers1

1
SELECT       c.*, r.*, 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
   LEFT JOIN booking_model                b  ON b.id = a.booking_model_id
GROUP BY     c.id, r.id
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Thanks, there are a couple of interesting tips in there. However it doesn't quite work. It only picks up cases where there is an assignment set in axis_revenue_stream_mappings and axis_channel_mappings. I need it to be either table or both. So if a revenue_stream is set but not a channel it will apply to all channels with that revenue stream. – Jeremy French Oct 30 '13 at 13:51
  • @JeremyFrench: Then surely you just need to change `AND` to `OR`? – eggyal Oct 30 '13 at 13:59
  • That selects too many, if I have 'booking model' b which is channel a and 'revenue_stream' X it will select all combinations. I have an non extendable solution based on yours I will post in the question. – Jeremy French Oct 30 '13 at 14:24