根据两个表上的值获取不同的数据集

nvbavucw  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(296)
SELECT users.firstname,
users.lastname,
users.username,
users.image_url,
posts.status,
posts.created_at as created_at,
posts.updated_at as updated_at,
share.post_user_id as user_id,
posts.id,
share.message as message,
share.user_id as share_user_id
FROM `share` INNER JOIN `posts` ON share.post_id = posts.id 
INNER JOIN `users` ON users.id = share.user_id

因此,正如您在这里看到的,我从users表中获取firstname、lastname和username,基于users.id=share.user\id。share表如下所示:
分享
消息
用户id
发布用户id
邮政编码
现在,如何根据users.id=share.post\u user\u id获取名称集?

aelbi1ox

aelbi1ox1#

您只需要使用别名(例如user2)对表用户进行另一个内部联接

SELECT users.firstname,
    users.lastname,
    users.username,
    users.image_url,
    posts.status,
    posts.created_at as created_at,
    posts.updated_at as updated_at,
    share.post_user_id as user_id,
    posts.id,
    share.message as message,
    share.user_id as share_user_id,
    users2.firstname,
    users2.lastname,
    users2.username,
    FROM `share` 
    INNER JOIN `posts` ON share.post_id = posts.id 
    INNER JOIN `users` ON users.id = share.user_id
    INNER JOIN `users`  AS user2 ON users2.id = post_user_id.user_id

相关问题