如何使用 LEFT JOIN 连接多个 mySQL 表？

debugcn 发表于 Dev

爱和平

桌子 projects

+----+--------+------------+
| id | title  | project_id |
+----+--------+------------+
|  1 | blue   |      12345 |
|  2 | red    |      67890 |
|  3 | yellow |      11111 |
|  4 | rosa   |      22222 |
+----+--------+------------+

桌子 connect

+----+------------+-----------+
| id | project_id | people_id |
+----+------------+-----------+
|  1 |      12345 |         4 |
|  2 |      12345 |         3 |
|  3 |      12345 |         2 |
|  4 |      22222 |         2 |
+----+------------+-----------+

桌子 people

+----+-----------+-----------+----------+
| id | firstname |   name    | position |
+----+-----------+-----------+----------+
|  1 | Diana     | Rose      | singer   |
|  2 | Al        | Capone    | singer   |
|  3 | Barbara   | Streisand | actor    |  
|  4 | Ben       | Harper    | musician |
+----+-----------+-----------+----------+

这是我希望的结果：

+----+---------+--------+----------+----------+
| id | project | singer | musician |  actor   |
+----+---------+--------+----------+----------+
|  1 | blue    | Capone | Harper   | Sreisand |
|  4 | rosa    | Capone |          |          |
+----+---------+--------+----------+----------+

我试图达到这样的结果：

<?php
$pdo = $db->query('
  SELECT *   
  FROM projects 
    LEFT JOIN connect ON projects.project_id=connect.project_id
    LEFT JOIN people ON connect.people_id=people.id;');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    echo <td>$row['title']</td>;
    echo "<td>";
    if ($row['position']== "singer"){echo $row['name'];}
    echo "</td>";
    echo "<td>";
    if ($row['position']== "musician"){echo $row['name'];}
    echo "</td>";
    echo "<td>";
    if ($row['position']== "actor"){echo $row['name'];}
    echo "</td>";
}
?>

但我的结果是：

+----+---------+--------+----------+----------+
| id | project | singer | musician |  actor   |
+----+---------+--------+----------+----------+
|  1 | blue    | Capone |          |          |
|  1 | blue    |        | Harper   |          |
|  1 | blue    |        |          | Sreisand |
|  4 | rosa    | Capone |          |          |
+----+---------+--------+----------+----------+

亚历克斯·布莱克斯

纯SQL解决方案：

SELECT res.id, res.project, 
  GROUP_CONCAT(res.singer) as singer, 
  GROUP_CONCAT(res.musician) as musician, 
  GROUP_CONCAT(res.actor) as actor
FROM (
  SELECT prj.id as id, prj.title as project, 
    IF(ppl.position = 'singer', ppl.name, null) as singer ,
    IF(ppl.position = 'musician', ppl.name, null) as musician,
    IF(ppl.position = 'actor', ppl.name, null) as actor
    FROM projects prj
      LEFT JOIN connect cnt ON prj.project_id=cnt.project_id
      LEFT JOIN people ppl ON cnt.people_id=ppl.id
) res
GROUP BY 1
HAVING singer IS NOT NULL OR musician IS NOT NULL OR actor IS NOT NULL
ORDER BY 1

如果你使用这个查询，php循环可以像这样简单

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    echo "<td>{$row['project']}</td><td>{$row['singer']}</td><td>{$row['musician']}</td><td>{$row['actor']}</td>";
}

该查询按 project.id 对所有人进行分组并连接姓名。子查询为每个位置设置名称。没有任何人的项目被过滤掉。使用inner join以下方法可以更有效地实现相同的结果：

SELECT res.id, res.project, 
  GROUP_CONCAT(res.singer) as singer, 
  GROUP_CONCAT(res.musician) as musician, 
  GROUP_CONCAT(res.actor) as actor
FROM (
  SELECT prj.id as id, prj.title as project, 
    IF(ppl.position = 'singer', ppl.name, null) as singer ,
    IF(ppl.position = 'musician', ppl.name, null) as musician,
    IF(ppl.position = 'actor', ppl.name, null) as actor
    FROM projects prj
      INNER JOIN connect cnt ON prj.project_id=cnt.project_id
      INNER JOIN people ppl ON cnt.people_id=ppl.id
) res
GROUP BY 1
ORDER BY 1

但OPleft join明确询问。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。