如何左联接三个具有相同列名的表 [英] How to LEFT JOIN three tables with the same column name

问题描述

我有三个未连接的表.其中两个将uid作为用户ID.问题是每次执行查询时都使用相同的用户ID.

I have a three tables that are left join. two of them have uid as user id. Problem is every time query execute it takes the same user id.

请检查表结构

-------------------------------------
|Users      |posts      |favorites  |
|-----------|-----------|-----------|
|user_id    |id         |id         |
|username   |title      |uid        |
|password   |post       |post_id    |
|           |uid        |           |
|           |favorites  |           |
-------------------------------------

MySQL查询

SELECT * FROM favorites 
LEFT JOIN users ON users.user_id = favorites.uid 
LEFT JOIN posts ON favorites.posts_id = posts.id
WHERE favorites.uid='$id' and posts.active=1

请注意，$ id是配置文件所有者(用户)ID.任何帮助将不胜感激.

please note that $id is the profile owner (user) id. Any help will be appreciated.

推荐答案

如果表如下所示(我已将user_id，post和favorites列重命名以澄清它们的作用，因为我理解它们)

If the table was as follows (I've renamed user_id, post, and favorites columns to clarify their roles as I understand them)

-------------------------------------
|Users      |posts      |favorites  |
|-----------|-----------|-----------|
|id         |id         |id         |
|username   |title      |uid        |
|password   |post_text  |post_id    |
|           |uid        |           |
|           |fav_id     |           |
-------------------------------------

此sql代码可用于获取您想要的(我认为)

This sql code can be used to get what (I think) you want

SELECT * FROM favorites 
LEFT JOIN posts ON  favorites.id = posts.fav_id
LEFT JOIN users ON posts.uid = users.id
WHERE favorites.uid='$id' and posts.active=1

这应该从给定用户ID的三个表中获取收藏夹帖子的所有详细信息.希望它对您有用.

This should get all the details of favorite posts from the three tables for the given user id. Hope it works for you.

这篇关于如何左联接三个具有相同列名的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！