SQL n:m relation

Question

I have the following tables (unrelated columns left out):

studios:
id | user_id
 1 |       1
 2 |       1

equipment:
id
 1
 2

studio_equipment:
id | studio_id | equipment_id
 1 |         1 |            1
 2 |         1 |            1

I have "studios" and "equipment". A studio belongs to a user. Equipment can be assigned to studios (studio_equipment table). An equipment can be assigned multiple times to a studio but there can also be studios that have no equipment yet.

I want to retrieve all studios for a certain user together with all the possible equipment that could be assigned to these studios. If an equipment has already been assigned to a studio, then show this aswell.

For the example above that would mean the following (for user_id 1):

desired results:
studio.id | equipment.id | studio_equipment.id
        1 |            1 |                   1
        1 |            1 |                   2
        1 |            2 |                null
        2 |            1 |                null
        2 |            2 |                null

This is my SQL statement thus far:

SELECT `s`.*, `e`.*, `se`.*
FROM (`studios` AS s)
LEFT JOIN `studio_equipment` AS se ON `s`.`id`=`se`.`studio_id`
LEFT OUTER JOIN `equipment` AS e ON `se`.`equipment_id`=`e`.`id`
WHERE `s`.`user_id` =  '1'

But this does not retrieve all the data i want. For Example studio 2 is retrieved but not paired with all the possible equipment.

Thanks in advance for your help!

Here's the info you need: http://stackoverflow.com/questions/219716/what-are-the-uses-for-cross-join — dcaswell, Sep 06 '13 at 13:08

score 1 · Accepted Answer · answered Sep 06 '13 at 13:20

SELECT 
    s.id  AS studio_id, 
    e.id  AS equipment_id, 
    se.id AS studio_equipment_id
FROM 
     studios AS s
  CROSS JOIN 
     equipment AS e
  LEFT JOIN 
     studio_equipment AS se 
       ON  se.studio_id = s.id
       AND se.equipment_id = e.id ;

SQL n:m relation

1 Answers1