SQL JOIN many-to-many

join, many-to-many, mysql, sql

Solution

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.

Problem

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

Original source