mysql case 连接,根据Mysql Case语句将一个表连接到另一个表

I have a table called PublicForum that 4 different users can post to, on this table I have 2 fields called AuthorId(int) and AuthorType(Enum) that shows the User Id and which user wrote the post.

Now I want to select All from PublicForum Join 1 of the 4 tables based on the AuthorType.

I have tried to do this with Case statement using the code below, but the other 3 tables are returned with NULL values and I want only the table that matches the AuthorType.

SELECT PublicForum.*, (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount,

Student.*, Parent.*, Teacher.*, Counsellor.* FROM PublicForum

LEFT JOIN Student ON (CASE WHEN PublicForum.AuthorType='Student' AND PublicForum.AuthorId = Student.StudentId THEN 1 ELSE 0 END = 1)

LEFT JOIN Parent ON (CASE WHEN PublicForum.AuthorType='Parent' AND PublicForum.AuthorId = Parent.ParentId THEN 1 ELSE 0 END = 1)

LEFT JOIN Teacher ON (CASE WHEN PublicForum.AuthorType='Teacher' AND PublicForum.AuthorId = Teacher.TeacherId THEN 1 ELSE 0 END = 1)

LEFT JOIN Counsellor ON (CASE WHEN PublicForum.AuthorType='Counsellor' AND PublicForum.AuthorId = Counsellor.CounsellorId THEN 1 ELSE 0 END = 1)

ORDER BY PublicForum.ForumId DESC LIMIT 10

Is there any other way to acheive this?

解决方案

You can't join conditionally. The way to do this is with separate queries that you combine using UNION.

SELECT t1.*, COUNT(pfc.ForumId) AS pfc_count

FROM (

SELECT p.*, s.StudentName AS Name, s.StudentCol2 AS Col2, s.StudentCol3 AS Col3, s.StudentCol4 AS Col4

FROM PublicForum AS p

JOIN Student AS s ON p.AuthorId = s.StudentId

WHERE p.AuthorType = 'Student'

UNION

SELECT p.*, pa.*

FROM PublicForum AS p

JOIN Parent AS pa ON p.AuthorId = pa.ParentId

WHERE p.AuthorType = 'Parent'

UNION

SELECT p.*, t.*

FROM PublicForum AS p

JOIN Teacher AS t ON p.AuthorId = t.TeacherId

WHERE p.AuthorType = 'Teacher'

UNION

SELECT p.*, c.*

FROM PublicForum AS p

JOIN Counsellor AS c ON p.AuthorId = c.CounsellorId

WHERE p.AuthorType = 'Counsellor') AS t1

LEFT JOIN PublicForumComments AS pfc ON t1.ForumId = pfc.ForumId

GROUP BY t1.ForumId

ORDER BY t1.ForumId DESC

LIMIT 10

The use of tablename.* for all the joined tables depends on them having the same number of columns, and all the columns are analogous and in the same order. The AS clauses in the first subquery then give a generic name to the columns coming from the UNION.