Sql Query to joining 4 tables using if condition

Refresh

April 2019

Views

45 time

0

I have 4 tables for the chat.

Table-1 (main comment table, the user for Table-1 is considered from Table-2)


id

comments


Table-2 (when ever new comment is added in Table-1, table-2 will also get updated with the user_id of the commenter)


comment_id(reference from Table-1)

user_id


Table-3(does some other works but related to Table-1 through comment_id)


id

column_related_to_table-3

comment_id

user_id


table-4(does some other works but related to Table-1 through comment_id)


id

column_related_to_table-4

comment_id

user_id


Now I want to get comment_id's from from all the Tables(Table-2, Table-3, Table-4) and finally get comments using those comment_id's from Table-1 if the particular user in present in either of the tables.

SELECT A.id,A.comment,
FROM Table-1 A  
LEFT JOIN Table-2 B 
ON B.comment_id = A.id
LEFT JOIN Table-3 C 
ON C.comment_id = A.id OR C.user_id = {$current_user}
LEFT JOIN Table-4 D
ON D.comment_id = A.id OR D.user_id = {$current_user}
WHERE  B.`user` = {$current_user}
GROUP BY A.`id`
ORDER BY A.`id` DESC
LIMIT 7 OFFSET 0

And i tried union of 3 tables as well

Select A.id AS cid
FROM Table-1 A
LEFT JOIN Table-2 B
ON B.comment_id = A.id
WHERE B.user_id = {$current_user}
UNION
Select C.comment_id AS cid
FROM Table-3 C
WHERE C.user_id = {$current_user}
UNION
Select D.comment_id AS cid
FROM Table-4 D
WHERE D.user_id = {$current_user} 

But here I don't know how to get the "GROUP BY A.id, ORDER BY A.id DESC LIMIT 7 OFFSET 0" working and don't know how to get the comments from Table-1 using the id's i get from union.

enter image description here

enter image description here

Table-1(activity) Table-2 (comments) Table-2(comments) id is connected to table-1(activity) as data-id. I am linking table-2(to know time and commented user etc) in table-1.

Table-3(chat) The concept here is like one comment can have thread that is represented by chat. It will always be related to comment. enter image description here

1 answers

0

Since you don't want any data other than comment Ids from Tables 2,3,4, why don't you use a simple IN clause?

SELECT * 
FROM Table1 A
WHERE A.id IN 
(SELECT Comment_Id FROM Table2 WHERE.... UNION ALL 
SELECT Comment_ID FROM Table3 WHERE... UNION ALL 
SELECT Comment_Id FROM Table4 WHERE...)
GROUP BY A.`id`
ORDER BY A.`id` DESC
LIMIT 7 OFFSET 0

If you do want to use additional columns from the other tables, you can get this working with a LEFT OUTER JOIN:

SELECT A.id,A.comment
FROM 
    Table-1 A  LEFT JOIN 
    Table-2 B ON B.comment_id = A.id AND B.User = currentUserId LEFT JOIN 
    Table-3 C ON C.comment_id = A.id AND C.User = currentUserId LEFT JOIN 
    Table-4 D ON D.comment_id = A.id AND D.User = currentUserId
WHERE  B.id IS NOT NULL OR C.id IS NOT NULL OR D.id IS NOT NULL
GROUP BY A.`id`
ORDER BY A.`id` DESC
LIMIT 7 OFFSET 0