mysql - 如何在 MySQL 中连接非规范化表？

Question

我有一张 table ，上面有不同的人。我想将它们连接到我的 house 表

房子表:

+-------+---------+---------+---------+
| house | person1 | person2 | person3 |
+-------+---------+---------+---------+
|     1 |       2 |       4 |       1 |
|     2 |       3 |       1 |       2 |
+-------+---------+---------+---------+

人表:

+----+------+
| id | name |
+----+------+
|  1 | fred |
|  2 | john |
|  3 | leo  |
|  4 | tom  |
+----+------+

这样的事情可能吗？

$pdo = $db->query('
 SELECT *
 FROM house
 LEFT JOIN people
 ON house.person1=people.id
 AND house.person2=people.id
 ;');
 while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    echo "Person 1 = ".$row['name'];
    echo "Person 2 = ".$row['name'];
 }

Answer 1

你很接近，你需要 OR 或 IN() 而不是 AND:

SELECT *
FROM house h
LEFT JOIN people p ON p.id IN(h.person1,h.person2,h.person3)

但我相信，更好的方法是按照评论中的建议更改表格设计。在这种情况下，您只需要:

SELECT *
FROM house h
LEFT JOIN people p ON h.person = p.id