Menu
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
799
In this case the two tables are joined using the relationship table1.id = table2.id . It is possible to use multiple join statements together to join more than one table at the same time. To do that you add a second INNER JOIN statement and a second ON statement to indicate the third table and the second relationship.
By definition, a many-to-many relationship is where more than one record in a table is related to more than one record in another table.
When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
