CODEWITHSUNDEEP
mysqlmany-to-manysubquery
enkdr
enkdr

Reputation: 363

mysql multiple many to many joins

I have events, events_styles, events_formats tables (an event can have many styles and many formats) I am trying to filter events which have a events_styles join and events_formats join. So the query should select all events which are a particular style AND a particular format - my attempts so far: 

SELECT * FROM events  
JOIN events_styles ON events.id = events_styles.event_id   
JOIN events_formats ON events.id = events_formats.format_id   
WHERE events_styles.style_id = 3  AND events_formats.format_id = 1;

Empty set (0.00 sec)

SELECT * FROM events_styles
WHERE events_styles.style_id = 3   
+----------+----------+
| event_id | style_id |
+----------+----------+
|        3 |        3 |
|        2 |        3 |
|        4 |        3 |
+----------+----------+
3 rows in set (0.00 sec)

SELECT * FROM events_formats
WHERE events_formats.format_id = 1    
+----------+-----------+
| event_id | format_id |
+----------+-----------+
|        1 |         1 |
|        3 |         1 |
|        4 |         1 |
+----------+-----------+
3 rows in set (0.00 sec)

So the first query should return the event with id 4? Im sure I need to combine 2nd and 3rd query into a subquery but Im unsure of the syntax - thanks

Upvotes: 2

Views: 408

Answers (1)

dash
dash

Reputation: 91482

You are very close!

Your query should be:

SELECT * FROM events   
JOIN events_styles ON events.id = events_styles.event_id    
JOIN events_formats ON events.id = events_formats.event_id   
WHERE events_styles.style_id = 3  
AND events_formats.format_id = 1;

By joining on format_id and not event_id you are only going to get event_id = 1 which has no correspoding style id of 3. That's where you went wrong :-)

Upvotes: 1

Related Questions