SQL JOIN 多对多

2021-12-17 00:00:00 join many-to-many sql mysql

对不起，标题太简约了，但我不知道如何简短地描述它.我有三张桌子:

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.

提前致谢

推荐答案

通过这个小技巧可以实现(多对多表上的 OUTER JOIN，约束条件是 GroupID 必须为 3(对于戏剧)

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 表示:从前面的表中取出所有行(位于 LEFT OUTER JOIN 左侧的那些行，如果你愿意的话)，即使下表中没有与它们对应的行.条件 ON elements.ID = group_elements.ElementID AND group_elements.GroupID = 3 表示如果我们找到与我们的 ElementID 匹配的任何内容，它也必须是一部戏剧 (GroupID = 3).然后我们对组表执行另一个 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.

相关文章