MySQL SQL JOIN 多对多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17774373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL JOIN many-to-many
提问by Martin Tram?ak
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
一切都很好，非常简单。我试图实现的 SELECT 如下
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 allthe elements from the table Elements and set the genre field to Dramaor null.
我想从元素表中选择所有元素并将流派字段设置为Drama或null。
I'm trying to do this in MySQL.
我正在尝试在MySQL 中执行此操作。
Thank you in advance
先感谢您
采纳答案by Miklos Aubert
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)
这个小技巧是可能的（多对多表上的 OUTER JOIN，约束条件是 GroupID 必须是 3（对于戏剧）
http://sqlfiddle.com/#!2/b7c18/2
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 JOINmeans : 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 = 3says 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.
LEFT OUTER JOIN意思是：从前面的表格中取出所有行（LEFT OUTER JOIN如果您愿意，在左侧的那些行），即使在下表中没有与它们对应的行。条件ON elements.ID = group_elements.ElementID AND group_elements.GroupID = 3表示，如果我们找到任何与我们的 ElementID 匹配的内容，它也必须是一部戏剧 (GroupID = 3)。然后我们在组表上执行另一个 LEFT OUTER JOIN，这使我们能够显示流派列，如果元素不是戏剧，则为 NULL。
