mysql - 在MySQL中如何使用中间表连接以获取第三个表数据(如果存在)？

Question

我不确定如何表达这个问题，所以让我举一个我的问题的例子。

我有:

SELECT p.id, p.name, p.info, r.name
  FROM p
  LEFT JOIN q ON q.p_name = p.name
  LEFT JOIN r ON r.id = q.r_id AND r.type = 'bar'
  GROUP BY p.id

对于表格，假设我有

p
+--+-----+-----+
|id|name |info |
+--+-----+-----+
| 1|'ABC'|'Yes'|
| 2|'DEF'|'No' |
| 3|'GHI'|'Myb'|
| 4|'JKL'|'Yes'|
+--+-----+-----+

q
+------+----+
|p_name|r_id|
+------+----+
|'ABC' | 2  |
|'ABC' | 3  |
|'ABC' | 1  |
|'DEF' | 3  |
|'DEF' | 4  |
|'HIJ' | 2  |
|'HIJ' | 3  |
+------+----+

r
+--+-----+-----+
|id|name |type |
+--+-----+-----+
| 1|'Cya'|'bar'|
| 2|'Hi' |'grt'|
| 3|'Bye'|'foo'|
| 4|'Ltr'|'bar'|
+--+-----+-----+

我想要结束的是:

desired output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1    | 'ABC'  | 'Yes'  | 'Cya'  |
| 2    | 'DEF'  | 'No'   | 'Ltr'  |
| 3    | 'GHI'  | 'Myb'  | NULL   |
| 4    | 'JKL'  | 'Yes'  | NULL   |
+------+--------+--------+--------+

当我尝试上面的查询时，我得到了这个等价物:

wrong output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1    | 'ABC'  | 'Yes'  | NULL   |
| 2    | 'DEF'  | 'No'   | NULL   |
| 3    | 'GHI'  | 'Myb'  | NULL   |
| 4    | 'JKL'  | 'Yes'  | NULL   |
+------+--------+--------+--------+

当我尝试通过添加“WHERE”子句来更改查询时

SELECT p.id, p.name, p.info, r.name
  FROM p
  LEFT JOIN q ON q.p_name = p.name
  LEFT JOIN r ON r.id = q.r_id
WHERE r.type = 'bar'
  GROUP BY p.id

我得到以下不正确的输出

incorrect output
+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1    | 'ABC'  | 'Yes'  | 'Cya'  |
| 2    | 'DEF'  | 'No'   | 'Ltr'  |
+------+--------+--------+--------+

如何及时得到想要的输出？

编辑:如果我删除“GROUP BY”，我将得到以下不正确的输出:

+------+--------+--------+--------+
| p.id | p.name | p.info | r.name |
+------+--------+--------+--------+
| 1    | 'ABC'  | 'Yes'  | NULL   |
| 1    | 'ABC'  | 'Yes'  | NULL   |
| 1    | 'ABC'  | 'Yes'  | 'Cya'  |
| 2    | 'DEF'  | 'No'   | NULL   |
| 2    | 'DEF'  | 'No'   | 'Ltr'  |
| 3    | 'GHI'  | 'Myb'  | NULL   |
| 3    | 'GHI'  | 'Myb'  | NULL   |
| 4    | 'JKL'  | 'Yes'  | NULL   |
+------+--------+--------+--------+

Answer 1

像这样堆叠连接:

SELECT p.id, p.name, p.info, r.name
FROM p
  LEFT JOIN (q LEFT JOIN r ON r.id = q.r_id AND r.type = 'bar')
  ON q.p_name = p.name
GROUP BY p.id