CODEWITHSUNDEEP
mysql
Diamond
Diamond

Reputation: 7483

Join one table to another based on Mysql Case Statement

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?

Upvotes: 0

Views: 102

Answers (2)

DaveTheRave
DaveTheRave

Reputation: 463

Maybe This? You may have to name the columns and pretty it up..

Select * from (
SELECT PublicForum.*,
 (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount, 
Student.* 
FROM PublicForum 
JOIN Student ON (CASE WHEN PublicForum.AuthorType='Student' AND PublicForum.AuthorId = Student.StudentId THEN 1 ELSE 0 END = 1) 
UNION 
SELECT PublicForum.*,
 (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount, 
Parent.*
FROM PublicForum 
JOIN Parent ON (CASE WHEN PublicForum.AuthorType='Parent' AND PublicForum.AuthorId = Parent.ParentId THEN 1 ELSE 0 END = 1) 
UNION
SELECT PublicForum.*,
 (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount, 
Teacher.*
FROM PublicForum 
JOIN Teacher ON (CASE WHEN PublicForum.AuthorType='Teacher' AND PublicForum.AuthorId = Teacher.TeacherId THEN 1 ELSE 0 END = 1) 
UNION
SELECT PublicForum.*,
 (SELECT COUNT(*) FROM PublicForumComment WHERE PublicForumComment.ForumId = PublicForum.ForumId) AS CommentCount, 
Counsellor.*
FROM PublicForum 
JOIN Counsellor ON (CASE WHEN PublicForum.AuthorType='Teacher' AND PublicForum.AuthorId = Teacher.TeacherId THEN 1 ELSE 0 END = 1) 
) as tmp1
ORDER BY ForumId DESC LIMIT 10

Upvotes: 1

Barmar
Barmar

Reputation: 782785

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.

Upvotes: 1

Related Questions