php mysql关联表_PHP mysql如何关联三个表显示来自不同表的输出

如果我理解正确,你想要一个带有feed表的外连接(为了保留所有帖子,即使没有相关的提要),然后GROUP BY post.pid,以便将每个帖子的所有这些提要合并在一起,并选择所需的信息.

我使用MySQL的GROUP_CONCAT()函数来获取所有用户(最多group_concat_max_len)的逗号分隔列表

给定帖子的“提要”(如果需要,可以使用SEPARATOR修饰符更改分隔符).

SELECT users.firstname, users.lastname,

users.screenname, posts.post_id, posts.user_id,

posts.post, posts.upload_name,

posts.post_type, posts.date_posted,

COUNT(feeds.user_id) AS friends, -- number of "likes"

SUM(feeds.user_id = ?) AS you, -- did I like this?

GROUP_CONCAT(feeds.user_id) -- who likes it?

FROM website.users users

INNER JOIN website.posts posts ON (users.user_id = posts.user_id)

LEFT JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)

GROUP BY posts.pid

ORDER BY posts.pid DESC

UPDATE

要获得“喜欢”帖子的用户的全名,不包括自己,需要第二次加入用户表：

SELECT users.firstname, users.lastname,

users.screenname, posts.post_id, posts.user_id,

posts.post, posts.upload_name,

posts.post_type, posts.date_posted,

COUNT(feeds.user_id) AS friends, -- number of "likes"

SUM(feeds.user_id = ?) AS you, -- did I like this?

GROUP_CONCAT(

CASE WHEN NOT likes.user_id = ? THEN -- exclude self

CONCAT_WS(' ', likes.firstname, likes.lastname) -- full names

END

)

FROM website.users users

INNER JOIN website.posts posts ON (users.user_id = posts.user_id)

LEFT JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)

LEFT JOIN website.users likes ON (feeds.user_id = likes.user_id)

GROUP BY posts.pid

ORDER BY posts.pid DESC