JOIN 与对右侧同一表的多个引用

Question

我有下表...

Table 1 (columns): user1 user2 user3 (each contain ids referencing to Table 2)
Table 2 (columns): id name phone email

我想从一个 MySQL 语句中获取表 2 中所有三个用户的姓名/电话/电子邮件。

示例数据：

表1

User1   User2   User3
Row 1    4       2       5
Row 2    1       3       3

表2

ID      Phone           Email
Row 1    1       777-940-2812    [e-mail]
Row 2    2       483-329-4939    [e-mail]
Row 3    3       849-393-9202    [e-mail]
Row 4    4       920-282-2039    [e-mail]
Row 5    5       102-203-4822    [e-mail]

一个 MySQL 语句中的预期结果：

U1Phone      U1Email     U2Phone      U2Email     U3Phone      U3Email
Row 1  920-282-2039 [e-mail] 483-329-4939 [e-mail] 102-203-4822 [e-mail]
Row 2  777-940-2812 [e-mail] 849-393-9202 [e-mail] 849-393-9202 [e-mail]

Answer 1

您需要多次JOINTable2。此外，Table1应该具有某种主键。那将是我认为你想ORDER BY的.

SELECT u1.name as U1Name, u1.phone as U1Phone, u1.email as U1Email,
u2.name as U2Name, u2.phone as U2Phone, u2.email as U2Email,
u3.name as U3Name, u3.phone as U3Phone, u3.email as U3Email
FROM Table1 t1
LEFT JOIN Table2 u1 ON t1.User1 = u1.id
LEFT JOIN Table2 u2 ON t1.User2 = u2.id
LEFT JOIN Table2 u3 ON t1.User3 = u3.id