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

Question

表项目

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

表连接

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

表人

+----+-----------+-----------+----------+
| 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 |          |          |
+----+---------+--------+----------+----------+

Answer 1

纯 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 对所有人员进行分组并连接姓名。子查询设置每个位置的名称。没有任何人的项目将被过滤掉。使用内连接可以更有效地获得相同的结果:

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

但是OP明确询问左连接。