我有2张桌子。第一个表包含大量用户数据。第二个表包含一个类别列表，以及职位是否已填补。
我需要显示带有用户ID和用户名的类。
我尝试做“连接”-但名称似乎彼此覆盖，因此每行只得到一个名称。
用户表：
+----------+---------+
| user_ID | name |
+----------+---------+
| 1 | Smith |
+----------+---------+
| 2 | Jones |
+----------+---------+
| 3 | Tim |
+----------+---------+
etc
类表：
+-------------+--------+--------+--------+---+---------+
| class_date | Spot 1 | Spot 2 | Spot 3 | . | Spot 16 |
+-------------+--------+--------+--------+---+---------+
| 2012/1/1 | 1 | 4 | 8 | . | 5 |
+-------------+--------+--------+--------+---+---------+
| 2012/2/1 | 2 | NULL | 1 | . | 3 |
+-------------+--------+--------+--------+---+---------+
| 2012/3/1 | 3 | 7 | NULL | . | NULL |
+-------------+--------+--------+--------+---+---------+
我要实现的目标：
+-------------+-------------+------------+--------------+---+------------+
| class_date | Spot 1 | Spot 2 | Spot 3 | . | Spot 16 |
+-------------+-------------+------------+--------------+---+------------+
| 2012/1/1 | 1 - Smith | 4 - Ben | 8 - Drew | . | 5 - Loz |
+-------------+-------------+------------+--------------+---+------------+
| 2012/2/1 | 2 - Jones | NULL | 1 - Smith | . | 3 - Tim |
+-------------+-------------+------------+--------------+---+------------+
| 2012/3/1 | 3 - Tim | 7 - Dan | NULL | . | NULL |
+-------------+-------------+------------+--------------+---+------------+
任何帮助将不胜感激
最佳答案
您需要为此使用LEFT JOIN。尝试，
SELECT a.class_date,
CONCAT(a.Spot1, ' - ', b.name) AS `SPOT 1`,
CONCAT(a.Spot2, ' - ', c.name) AS `SPOT 2`,
CONCAT(a.Spot3, ' - ', d.name) AS `SPOT 3`,
... -- keep doing until last column (i guess up to 16)
FROM ClassTable a
LEFT JOIN `UserTable` b
ON a.Spot1 = b.user_ID
LEFT JOIN `UserTable` c
ON a.Spot2 = c.user_ID
LEFT JOIN `UserTable` d
ON a.Spot3 = d.user_ID
... ... -- keep doing until last column (i guess up to 16)
关于mysql - MySQL表上的多个联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12076345/