SQL JOIN many-to-many

Question

Sorry about the minimalistic title but I don't know how to describe it in short. I have three tables:

The table of groups

ID | Genre ----------------- 1  | Action 2  | Adventure 3  | Drama 

Many to many table

GroupID | ElementID -----------------     3   |    1     1   |    2     2   |    2     2   |    3     3   |    3 

And the table of elements

ID | Element ----------------- 1  | Pride and Prejudice 2  | Alice in Wonderland 3  | Curious Incident Of A Dog In The Night Time 

All is fine and very simple. The SELECT I am trying to achieve is the following

ID | Element                                         |  Genre ------------------------------------------------------------- 1  | Pride and Prejudice                             | Drama 2  | Alice in Wonderland                             | NULL 3  | Curious Incident Of A Dog In The Night Time     | Drama 

I want to select all the elements from the table Elements and set the genre field to Drama or null.

I'm trying to do this in MySQL.

Thank you in advance

Answer 1

It's possible with this little trick (OUTER JOIN on the many-to-many table, with the constraint that the GroupID has to be 3 (for Drama)

http://sqlfiddle.com/#!9/01cf3/1

SELECT elements.ID, elements.Element, groups.Genre   FROM elements LEFT OUTER JOIN group_elements   ON elements.ID = group_elements.ElementID  AND group_elements.GroupID = 3 LEFT OUTER JOIN groups   ON group_elements.GroupID = groups.ID 

LEFT OUTER JOIN means : take all the lines from the tables that preceded (the ones that are on the LEFT hand side of the LEFT OUTER JOIN, if you will), even if there's no lines corresponding to them in the following tables. The condition ON elements.ID = group_elements.ElementID AND group_elements.GroupID = 3 says that if we find anything that matches our ElementID, it also must be a drama (GroupID = 3). We then do another LEFT OUTER JOIN on the groups table, which enables us to display the Genre column, or NULL if the element was not a drama.