I have 2 tables.
One contains posts, another replies to those posts. I was trying to draw a list of posts based on a particular criteria and if the user commented on a post mark this line in my output table. If the user got a reply on his/her comment mark it again with a different icon.
My original query looked something like this:
SELECT
t1.ID,
t1.synopsis,...
t2.user_comment,
t2.user_reply
FROM t1
LEFT JOIN t2
ON (t2.id = t1.ID)
WHERE ...
ORDER BY call_exp ASC
However, because there could be comments to the same post from multiple users, I noticed that my list started to grow, by multiplying the same entry by the number of comments. i.e.
was
post 1
post 2
post3
etc.
now became
post 1
post 1
post 1
post 1
post 2
post3
post3
etc.
So I decided to modify query to avoid duplicates by adding DISTINCT and GROUP BY
SELECT
DISTINCT t1.ID,
t1.synopsis,...
t2.user_comment,
t2.user_reply
FROM t1
LEFT JOIN t2
ON (t2.id = t1.ID)
WHERE ...
GROUP BY t1.ID
ORDER BY call_exp ASC
But now I see that I cut short the query looking through t2 if this particular user made a comment and if this particular user got reply.
Now, the easy fix for this situation would be to run a query on t1 and output the relevant posts through the loop and then run a separate query in t2 for each post line and see if there is an entry associated with this user id, but I am afraid it would slow down performance considerably and that's why I decided to combine the two queries in one.
I feel I'm very close but I need some help here.
😕
