评论和回复，加入3表查询

Question

I have been searching now for a while, but still did not find an optimal solution. I have three tables: posts, membership and comments. I want to have a Comment and reply system.

These are my tables:

Main posts:

post_id | image_url | image_thumb_url | title | text | date | sender_id 

Membership:

user_id | username | pw | email | 

Comments (replies) to posts:

com_id | comment | post_id_fk | user_id_fk | ip | date

I currently have a query for retrieving the posts with the membership data:

SELECT * FROM `posts`
LEFT JOIN `membership` ON `posts`.`sender_id` = `membership`.`user_id`
UNION SELECT * FROM `posts`
      RIGHT JOIN `membership` ON `posts`.`sender_id` = `membership`.`user_id`
      ORDER BY `post_id` DESC LIMIT $limit $start;

I now want to add the replies to each posts, containing the membership data. Does anyone have suggestion or perhaps a better solution ?

Answer 1

SELECT * FROM posts AS P
LEFT JOIN comments AS C
ON P.post_id = C.post_id_fk
LEFT JOIN membership AS POST_MEMBER
ON POST_MEMBER.user_id = P.sender_id
LEFT JOIN membership AS REPLY_MEMBER
ON REPLY_MEMBER.user_id = C.user_id_fk