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

Question

table projects

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

table connect

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

table people

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

This is the result I wish:

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

I tried to achieve the result like this:

<?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>";
}
?> 

But my result is:

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

Answer 1

Pure SQL solution:

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

If you use this query, the php loop can be as simple as

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

The query groups all the people by project.id and concatenates names. The sub-query sets names per position. Projects without any person are filtered out. The same result can be achieved more efficiently using 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

but OP asked about left join explicitly.