With the following tables
posts
comments
replies
I am trying to get every comment and reply from each post.post_id and post.user_id='x' and the comment or reply is not deleted(0)
this is what i have tried
SELECT *
FROM posts p
LEFT JOIN comments c ON p.id=c.post_id
LEFT JOIN replies r ON p.id=r.post_id
WHERE p.user_id=$user_id
&& c.deleted='0' && r.deleted='0'
which does not work...
You need to put the deleted check into the join clause. This should do it:
SELECT *
FROM posts p
LEFT JOIN comments c ON c.post_id = p.post_id AND NOT c.deleted
LEFT JOIN replies r ON r.post_id = p.post_id AND NOT r.deleted
WHERE p.user_id = $user_id
Note: Not sure if c.post_id joins to p.id or p.post_id - chenge the on clause as required
A post may have comments or not. Use LEFT JOIN instead of INNER JOIN.
A post may have replies or not. Use LEFT JOIN instead of INNER JOIN in that join too.
When LEFT JOIN is used, a condition like WHERE comments.deleted = 0 that includes a field (from the right table (comments) in the LEFT JOIN), the LEFT JOIN is cancelled. So, we should put this condition in the ON clause and not in the WHERE.
SELECT *
FROM posts p
LEFT JOIN comments c
ON p.post_id = c.post_id
AND c.deleted = 0
LEFT JOIN replies r
ON p.post_id = r.post_id
AND r.deleted = 0
WHERE p.user_id = $user_id
Thinking more clearly, the above will show what the question describes but in cases with say, 4 comments and 3 replies, 12 rows will be returned (3x4). Which is probably not wanted. The following 2nd try does not have such issue.
I don't see a post.text or comment.text or reply.text in the tables but anyway, you'll get the idea. You can remove the 3 text lines if not appropriate.
( SELECT p.post_id AS post_id
, 0 AS type
, p.post_id AS id
, p.text AS text
FROM posts p
WHERE p.user_id = $user_id
)
UNION ALL
( SELECT p.post_id AS post_id
, 1 AS type
, c.comment_id AS id
, c.text AS text
FROM posts p
JOIN comments c
ON p.post_id = c.post_id
WHERE p.user_id = $user_id
AND c.deleted = 0
)
UNION ALL
( SELECT p.post_id AS post_id
, 2 AS type
, r.reply_id AS id
, r.text AS text
FROM posts p
JOIN replies r
ON p.post_id = r.post_id
WHERE p.user_id = $user_id
AND r.deleted = 0
)
ORDER BY post_id
, post_type
, id
The 0,1,2 stand for post, comment, reply.
ANDinstead of&&deleted=0means deleted comment, then this will show all deleted-posts - deleted-comments combinations.0mean? Deleted or not deleted?