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

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.

Comments

[TD_Nijboer]

i don't see the need for returning a NULL column. why not use a WHERE groups.ID != '3' or something. bisides that, when running you're fiddle in Mysql 5.6 it will return in different order! first the non NULL columns, than the NULL column. sqlfiddle.com/#!9/01cf3/1/0 u could use a ORDER BY statement.

[Miklos Aubert]

My reply answered the needs of the question, which called for NULL values to be displayed when the genre of the book (assuming those are books) isn't known. Queries with outer joins are useful when you want to see all the data from one of the tables in the query, no matter if there's any related data elsewhere. For example, the above query may be useful in an application that manages a book collection : it lets you see all the books and identify those you haven't tagged with a genre yet. Seeing the genres for books that are tagged can help you decide on the genre of another book.

[patricknelson]

If you're interested in also getting all genres in the result (like I was), you can augment TD_Nijbour's query to also include GROUP_CONCAT on the values you want together and then GROUP BY to roll up the results on the base records. For example: sqlfiddle.com/#!9/036d49/2/0