MySql -连接2个表，其中第二个表连接两次 - MySql - Joining 2 Tables where 2nd table is joined twice

MySql - Joining 2 Tables where 2nd table is joined twice

我有两个表(studentclass和user)

表studentclass

columns (id, class_name, user_schoolid_faculty, user_schoolid_student)

表用户

columns (id, firstname, lastname, schoolid, position)
user_schoolid_faculty is a foreign key from schoolid
user_schoolid_student is a foreign key from schoolid

我的第一个查询是

 SELECT * FROM studentclass WHERE class_name = '$class_name'

我想使用schoolid查看用户的姓和名的数据

 SELECT studentclass.class_name, user.firstname, user.lastname
 FROM studentclass
 INNER JOIN user
 ON studentclass.user_schoolid_faculty = user.schoolid
 WHERE user.position = 'Faculty'
 LEFT JOIN user
 ON studentclass.user_schoolid_student = user.schoolid
 WHERE user.position = 'Student'

抛出错误，not unique table/alias 'user'

对于SQL Server，保留关键字user使用方括号。
还要为表添加别名。

SELECT SC.class_name, U1.firstname, U2.lastname
FROM studentclass SC
INNER JOIN [user] U1 ON SC.user_schoolid_faculty = U1.schoolid
LEFT JOIN [user] U2  ON SC.user_schoolid_student = U2.schoolid AND U2.position = 'Student'
WHERE U1.position = 'Faculty'

为MySQL:

SELECT SC.class_name, U1.firstname, U2.lastname
FROM studentclass SC
INNER JOIN `user` U1 ON SC.user_schoolid_faculty = U1.schoolid
LEFT JOIN `user` U2  ON SC.user_schoolid_student = U2.schoolid AND U2.position = 'Student'
WHERE U1.position = 'Faculty'

对于同一个表user，您应该使用不同的别名，当您使用JOIN时，您应该使用ON来合并两个表，而不是WHERE，试试这个;)

SELECT studentclass.class_name, user1.firstname, user2.lastname
FROM studentclass
INNER JOIN user user1
ON studentclass.user_schoolid_faculty = user1.schoolid
AND user1.position = 'Faculty'
LEFT JOIN user user2
ON studentclass.user_schoolid_student = user2.schoolid
AND user2.position = 'Student'

还有一点不清楚，为什么在这个查询中使用一个INNER JOIN和一个LEFT JOIN ?

你必须为你的表使用别名:

SELECT studentclass.class_name, u1.firstname, u1.lastname
FROM studentclass
 INNER JOIN user u1
 ON studentclass.user_schoolid_faculty = u1.schoolid
 WHERE user.position = 'Faculty'
 LEFT JOIN user u2
 ON studentclass.user_schoolid_student = u2.schoolid
 WHERE u1.position = 'Student'