Mysql LEFT JOIN的三个表返回许多Rows [英] Mysql LEFT JOIN of three tables returns to many Rows
问题描述
我在使用Mysql自从一段时间，我真的很困惑的简单的LEFT JOIN在三个表的结果。
我有以下三个表我创建了一个例子，缩小范围）
a）人
+ ---------- + ------------- + ------ + ----- + --------- + ---------------- +
|字段|类型| Null | Key |默认|额外|
+ ---------- + ------------- + ------ + ----- + ------- - + ---------------- +
| PersonID | int（11）| NO | PRI | NULL | auto_increment |
|名称| varchar（50）| YES | | NULL | |
|年龄| int（11）| YES | | NULL | |
+ ---------- + ------------- + ------ + ----- + ------- - + ---------------- +
b ）person_fav_artists
+ ---------------- + ------ -------- + ------ + ----- + --------- + ---------------- +
|字段|类型| Null | Key |默认|额外|
+ ---------------- + -------------- + ------ + ----- + --------- + ---------------- +
| FavInterpretID | int（10）| NO | PRI | NULL | auto_increment |
| PersonID | int（10）| NO | | 0 | |
|解释| varchar（100）| YES | | NULL | |
+ ---------------- + -------------- + ------ + ----- + --------- + ---------------- +
c）person_fav_movies
+ ------------ + --- ----------- + ------ + ----- + --------- + --------------- - +
|字段|类型| Null | Key |默认|额外|
+ ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- +
| FavMovieID | int（10）| NO | PRI | NULL | auto_increment |
| PersonID | int（10）| NO | | 0 | |
|电影| varchar（100）| YES | | NULL | |
+ ------------ + -------------- + ------ + ----- + ---- ----- + ---------------- +
我的示例表用于存储任意数量的艺术家和电影给一个人。
天气这使sence或不是真的很重要，因为它只是一个简单的例子。
现在我在表中有以下数据： / p>
mysql> SELECT * FROM persons;
+ ---------- + ------ + ------ +
| PersonID |名称|年龄|
+ ---------- + ------ + ------ +
| 1 | Jeff | 22 |
| 2 | Lisa | 15 |
| 3 | Jon | 30 |
+ ---------- + ------ + ------ +
mysql> SELECT * FROM person_fav_artists;
+ ---------------- + ---------- + ---------------- +
| FavInterpretID | PersonID |解释|
+ ---------------- + ---------- + ---------------- +
| 1 | 1 | Linkin Park |
| 2 | 1 |缪斯|
| 3 | 2 |麦当娜|
| 4 | 2 |凯蒂·佩里|
| 5 | 2 |布兰妮斯皮尔斯|
| 6 | 1 | Fort Minor |
| 7 | 1 | Jay Z |
+ ---------------- + ---------- + ---------------- +
mysql> SELECT * FROM person_fav_movies;
+ ------------ + ---------- + ------------------- +
| FavMovieID | PersonID |电影|
+ ------------ + ---------- + ------------------- +
| 1 | 1 |美国饼1 |
| 2 | 1 |美国饼2 |
| 3 | 1 |美国饼3 |
| 4 | 3 |游戏的权力|
| 5 | 3 | Eragon |
+ ------------ + ---------- + ------------------- +
现在我只是简单地加入表与以下查询：
选择* FROM persons
LEFT JOIN person_fav_artists USING（PersonID）
LEFT JOIN person_fav_movies USING（PersonID）;
这会返回以下结果：
+ ---------- + ------ + ------ + --------------- - + ---------------- + ------------ + ------------------ - +
| PersonID |名称|年龄| FavInterpretID |解释| FavMovieID |电影|
+ ---------- + ------ + ------ + ---------------- + --- ------------- + ------------ + ------------------- +
| 1 | Jeff | 22 | 1 | Linkin Park | 1 |美国饼1 |
| 1 | Jeff | 22 | 1 | Linkin Park | 2 |美国饼2 |
| 1 | Jeff | 22 | 1 | Linkin Park | 3 |美国饼3 |
| 1 | Jeff | 22 | 2 |缪斯| 1 |美国饼1 |
| 1 | Jeff | 22 | 2 |缪斯| 2 |美国饼2 |
| 1 | Jeff | 22 | 2 |缪斯| 3 |美国饼3 |
| 1 | Jeff | 22 | 6 | Fort Minor | 1 |美国饼1 |
| 1 | Jeff | 22 | 6 | Fort Minor | 2 |美国饼2 |
| 1 | Jeff | 22 | 6 | Fort Minor | 3 |美国饼3 |
| 1 | Jeff | 22 | 7 | Jay Z | 1 |美国饼1 |
| 1 | Jeff | 22 | 7 | Jay Z | 2 |美国饼2 |
| 1 | Jeff | 22 | 7 | Jay Z | 3 |美国饼3 |
| 2 | Lisa | 15 | 3 |麦当娜| NULL | NULL |
| 2 | Lisa | 15 | 4 |凯蒂·佩里|空| NULL |
| 2 | Lisa | 15 | 5 |布兰妮斯皮尔斯| NULL | NULL |
| 3 | Jon | 30 | NULL | NULL | 4 |游戏的权力|
| 3 | Jon | 30 | NULL | NULL | 5 | Eragon |
+ ---------- + ------ + ------ + ---------------- + --- ------------- + ------------ + ------------------- +
集合中的17行（0.00秒）
到目前为止很好。
我的问题是现在如果它是正常的，'12'行返回的人'Jeff'，尽管事实上，他只有四个'艺术家'和三个电影分配给他。
我想我可以理解为什么结果是这样，但我认为这么多的行返回这么少的实际数据是非常愚蠢的。
我想要的结果会像下面这样（只适用于Jeff）：
+ ---------- + ------ + ------ + ---------------- + ---------------- + ------------ + --- ---------------- +
| PersonID |名称|年龄| FavInterpretID |解释| FavMovieID |电影|
+ ---------- + ------ + ------ + ---------------- + --- ------------- + ------------ + ------------------- +
| 1 | Jeff | 22 | 1 | Linkin Park | 1 |美国饼1 |
| 1 | Jeff | 22 | 2 |缪斯| 2 |美国饼2 |
| 1 | Jeff | 22 | 3 | Fort Minor | 3 |美国饼3 |
| 1 | Jeff | 22 | 4 | Jay Z | 1 | NULL | < - 'American Pie 1/2/3'也会OK。
+ ---------- + ------ + ------ + ---------------- + --- ------------- + ------------ + ------------------- +
解决方案 / div> 您正在得到正确的结果与12记录，因为是正确的元组与你要求的数据的方式。我不知道为什么你连接这三个表在一起，因为固有的是，2相关表是不同类型的数据。我建议的是，你选择人&电影，然后你可以联合的人&艺术家，因为你的联合将希望列是相同的，我建议添加一个类型来区分艺术家和电影，然后漂亮的名称应该只是AS string_value
I´m using Mysql since quite a while and am really confused by the result of a simple LEFT JOIN on three Tables.
I have the following three tables (I created an example, to narrow it down)
a) persons
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| PersonID | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
b) person_fav_artists
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| FavInterpretID | int(10) | NO | PRI | NULL | auto_increment |
| PersonID | int(10) | NO | | 0 | |
| Interpret | varchar(100) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
c) person_fav_movies
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| FavMovieID | int(10) | NO | PRI | NULL | auto_increment |
| PersonID | int(10) | NO | | 0 | |
| Movie | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
My example tables are used to store an any number of artists and movies to a single person.
Weather this makes sence or not doesn´t really matter since it´s just a simple example.
Now I have the following data in the tables:
mysql> SELECT * FROM persons;
+----------+------+------+
| PersonID | Name | Age |
+----------+------+------+
| 1 | Jeff | 22 |
| 2 | Lisa | 15 |
| 3 | Jon | 30 |
+----------+------+------+
mysql> SELECT * FROM person_fav_artists;
+----------------+----------+----------------+
| FavInterpretID | PersonID | Interpret |
+----------------+----------+----------------+
| 1 | 1 | Linkin Park |
| 2 | 1 | Muse |
| 3 | 2 | Madonna |
| 4 | 2 | Katy Perry |
| 5 | 2 | Britney Spears |
| 6 | 1 | Fort Minor |
| 7 | 1 | Jay Z |
+----------------+----------+----------------+
mysql> SELECT * FROM person_fav_movies;
+------------+----------+-------------------+
| FavMovieID | PersonID | Movie |
+------------+----------+-------------------+
| 1 | 1 | American Pie 1 |
| 2 | 1 | American Pie 2 |
| 3 | 1 | American Pie 3 |
| 4 | 3 | A Game of Thrones |
| 5 | 3 | Eragon |
+------------+----------+-------------------+
Now i´m simply joining the tables with the following query:
Select * FROM persons
LEFT JOIN person_fav_artists USING (PersonID)
LEFT JOIN person_fav_movies USING (PersonID);
which returns the following result:
+----------+------+------+----------------+----------------+------------+-------------------+
| PersonID | Name | Age | FavInterpretID | Interpret | FavMovieID | Movie |
+----------+------+------+----------------+----------------+------------+-------------------+
| 1 | Jeff | 22 | 1 | Linkin Park | 1 | American Pie 1 |
| 1 | Jeff | 22 | 1 | Linkin Park | 2 | American Pie 2 |
| 1 | Jeff | 22 | 1 | Linkin Park | 3 | American Pie 3 |
| 1 | Jeff | 22 | 2 | Muse | 1 | American Pie 1 |
| 1 | Jeff | 22 | 2 | Muse | 2 | American Pie 2 |
| 1 | Jeff | 22 | 2 | Muse | 3 | American Pie 3 |
| 1 | Jeff | 22 | 6 | Fort Minor | 1 | American Pie 1 |
| 1 | Jeff | 22 | 6 | Fort Minor | 2 | American Pie 2 |
| 1 | Jeff | 22 | 6 | Fort Minor | 3 | American Pie 3 |
| 1 | Jeff | 22 | 7 | Jay Z | 1 | American Pie 1 |
| 1 | Jeff | 22 | 7 | Jay Z | 2 | American Pie 2 |
| 1 | Jeff | 22 | 7 | Jay Z | 3 | American Pie 3 |
| 2 | Lisa | 15 | 3 | Madonna | NULL | NULL |
| 2 | Lisa | 15 | 4 | Katy Perry | NULL | NULL |
| 2 | Lisa | 15 | 5 | Britney Spears | NULL | NULL |
| 3 | Jon | 30 | NULL | NULL | 4 | A Game of Thrones |
| 3 | Jon | 30 | NULL | NULL | 5 | Eragon |
+----------+------+------+----------------+----------------+------------+-------------------+
17 rows in set (0.00 sec)
So far so good.
My question is now if it´s "normal" that '12' Rows are returned for the person 'Jeff' despite the fact that he only has four 'artists' and three 'movies' assigned to him.
I think I may understand why the result is as it is, but I think it´s quite stupid to return so many Rows for so less actual data.
So is there something wrong with my query or is this behaviour on purpose?
The result I´d like to have would be like the following (only for Jeff):
+----------+------+------+----------------+----------------+------------+-------------------+
| PersonID | Name | Age | FavInterpretID | Interpret | FavMovieID | Movie |
+----------+------+------+----------------+----------------+------------+-------------------+
| 1 | Jeff | 22 | 1 | Linkin Park | 1 | American Pie 1 |
| 1 | Jeff | 22 | 2 | Muse | 2 | American Pie 2 |
| 1 | Jeff | 22 | 3 | Fort Minor | 3 | American Pie 3 |
| 1 | Jeff | 22 | 4 | Jay Z | 1 | NULL | <- 'American Pie 1/2/3' would be OK as well.
+----------+------+------+----------------+----------------+------------+-------------------+
Thanks for your help!
解决方案 You are getting the correct result with the 12 records becuase that is the correct tuple with the way you are asking for the data. I am not sure why you are joinming these 3 tables together becuase inherently, the 2 related tables are not the same type of data. What I would suggest is that you select person & movies and then you can union person & artists, becuase your union will want the columns to be the same, i would suggest adding a type to differentiate from artists and movies and then the nice name should just be AS a string_value
这篇关于Mysql LEFT JOIN的三个表返回许多Rows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
