PHP MySQL double inner join

Question

I have three tables:

posts

• id
• post_id
• user_id

comments

• id
• post_id
• comment_id
• user_id
• deleted

replies

• id
• post_id
• reply_id
• user_id
• deleted

I am trying to get all comments and replies from each post.post_id with post.user_id=x.

I tried:

    SELECT *
    FROM posts AS p
    INNER JOIN comments as c
    ON c.comment_id=p.post_id
    INNER JOIN replies as r
    ON r.reply_id=p.post_id
    WHERE
    p.user_id='x'

which returns 0...

---

The solution was

SELECT *
FROM POSTS A
LEFT JOIN COMMENTS B ON A.POST_ID=B.COMMENT_ID
LEFT JOIN REPLIES C ON A.POST_ID=C.REPLY_ID
WHERE A.USER_ID='X'

So if I add a deleted column on the comments and replies tables, how can I check if the comment or reply I am getting is not deleted?

I tried adding after A.USER_ID='X' && B.deleted='0' && C.deleted='0'

But it returns 0.

Answer 1

You're missing a key relationship in your model. You need to have a column in replies and comments for post_id, and then join the tables on post_id.

Once you've made this change, then your query would look like this:

SELECT c.*, r.* 
FROM posts p
INNER JOIN comments c ON p.id=c.post_id
INNER JOIN replies r ON p.id=r.post_id
WHERE p.user_id=$user_id