SQL JOIN多对多 [英] SQL JOIN many-to-many

问题描述

抱歉，简约标题很简短，但我不知道该如何形容.我有三个表:

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

分组表

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

多对多表

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

元素表

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

一切都很好，非常简单.我要实现的SELECT如下

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.

我正在尝试在 MySQL 中执行此操作.

I'm trying to do this in MySQL.

提前谢谢

推荐答案

有这个小技巧(在多对多表上进行外部联接，并且GroupID必须为3(对于Drama)为约束

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/#!2/b7c18/2

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的意思是:即使下表中没有与之相对应的行，也要取用之前表中的所有行(如果愿意，请位于LEFT OUTER JOIN的左侧). .条件ON elements.ID = group_elements.ElementID AND group_elements.GroupID = 3表示，如果我们找到与ElementID匹配的任何内容，那么它也必须是一部戏剧(GroupID = 3).然后，我们在groups表上执行另一个LEFT OUTER JOIN，这使我们能够显示Genre列，如果该元素不是戏剧，则显示NULL.

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.

这篇关于SQL JOIN多对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！