PHP MySQL double internal join

Question

I have three tables:

posts

• I'd
• post_id
• User ID

Comments

• I'd
• post_id
• comment_id
• User ID
• remote

the answers

• I'd
• post_id
• reply_id
• User ID
• remote

I am trying to get all comments and answers from everyone post.post_idwith post.user_id=x.

I did my best:

    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 decision 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'

Therefore, if I add a deleted column to the comment and response tables, how can I check if my comment or response is deleted?

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

But it returns 0.

Answer 1

You are missing a key attitude in your model. You should have a column in the answers and comments for post_id, and then join the tables in post_id.

, :

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

Answer 2

. POSTS, WHERE.

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'

Answer 3

From what I see, post_id is missing from all tables. Too many identifiers.

Modify the table so that the relationship is obvious, and then you can easily extract the data.