php - 如何左连接来自同一个 mySQL 表的多个数据？

Question

这是我的 table 位置:

+----+---------+------+--------+---------+
| id | teacher | cook | doctor | dentist |
+----+---------+------+--------+---------+
|  1 |       3 |    4 |      2 |       1 |
+----+---------+------+--------+---------+

这是我的表人:

+----+-----------+--------+-----+
| id | firstname |  name  | age |
+----+-----------+--------+-----+
|  1 | Fred      | Miller |  42 |
|  2 | Emily     | Rose   |  32 |
|  3 | Ben       | Harper |  38 |
|  4 | Samanta   | Jones  |  35 |
+----+-----------+--------+-----+

mySQL数据库请求

$pdo = $db->query('
SELECT *, position.id AS id, people.id AS people_id 
FROM position 
LEFT JOIN people 
ON position.teacher=people.id;
ON position.cook=people.id;
ON position.doctor=people.id;
ON position.dentist=people.id;
');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
echo "The teacher is "$row['firstname']." ".$row['name'];
echo "The cook is "$row['firstname']." ".$row['name'];
echo "The doctor is "$row['firstname']." ".$row['name'];
echo "The dentist is "$row['firstname']." ".$row['name'];
}

我的结果是:

The teacher is Ben Harper
The cook is Ben Harper
The doctor is Ben Harper
The dentist is Ben Harper

我需要的结果:

The teacher is Ben Harper
The cook is Samanta Jones
The doctor is Emiliy Rose
The dentist is Fred Miller

Answer 1

如果你重组你的表格会更好。

+----+------------+| id | position   |       +----+------------||  1 |  teacher   ||  2 |  cook      ||  3 |  doctor    ||  4 |  dentist   |+----+------------++----+-----------+--------+-----+-------------+| id | firstname |  name  | age | position_id |+----+-----------+--------+-----+-------------+|  1 | Fred      | Miller |  42 |  4          ||  2 | Emily     | Rose   |  32 |  3          ||  3 | Ben       | Harper |  38 |  1          ||  4 | Samanta   | Jones  |  35 |  2          |+----+-----------+--------+-----+-------------+

Here you can have a foreign key reference on people.position_id and position.id.This way you can have many people with same position.

SELECT position.id id
     , people.id people_id
     , people.firstname
     , people.name
  FROM position 
  LEFT 
  JOIN people 
    ON people.position_id = position.id;