连接4个表
连接4个表
提问于 2012-04-13 05:59:42
我对这种关系型的数据库设计很陌生。我刚刚用这种方式设计了数据库。然而，我对这个JOIN of MySQL感到非常困惑。加入这个表的查询应该是什么？如果可以看到表，则users是所有表的引用。
用户
+----------+----------------+-----------------+
| users_id | users_level_id | users_status_id |
+----------+----------------+-----------------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+----------+----------------+-----------------+users_credentials
+----------+---------------------------+-----------------------------+----------------------------+
| users_id | users_credential_username | users_credential_email | users_credential_password |
+----------+---------------------------+-----------------------------+----------------------------+
| 1 | super | super@gmail.com | $5$e94e9e$vptscyHjm8rdX0j6 |
| 2 | admin | admin@gmail.com | $5$fVuOmySyC0PttbiMn8in0k7 |
+----------+---------------------------+-----------------------------+----------------------------+users_level
+----------------+-------------------------+
| users_level_id | users_level_description |
+----------------+-------------------------+
| 1 | Super Administrator |
| 2 | Administrator |
+----------------+-------------------------+users_status
+-----------------+--------------------------+
| users_status_id | users_status_description |
+-----------------+--------------------------+
| 0 | Disabled |
| 1 | Enabled |
+-----------------+--------------------------+回答 5
Stack Overflow用户
回答已采纳
发布于 2012-04-13 06:03:36
尝尝这个
SELECT u.*, uc.*, ul.*, us.*
FROM users u
INNER JOIN users_credentials uc
ON u.users_id = uc.users_id
INNER JOIN users_level ul
ON u.users_level_id = ul.users_level_id
INNER JOIN users_status us
ON u.users_status_id = us.users_status_id注意，使用INNER JOIN：这意味着如果用户在联接表上没有对应的记录，就不会显示它；如果您需要返回每个用户，即使没有相关表上的匹配记录，也可以用LEFT JOIN更改INNER JOIN。
用户评论后编辑的：
如果您只想返回某个列，请将其定义为下面的示例
SELECT uc.users_credential_username AS username,
uc.users_credential_email AS email,
uc.users_credential_password AS pwd,
ul.users_level_description AS level,
us.users_status_description AS statusStack Overflow用户
发布于 2012-04-13 06:04:47
我想这个看起来是这样的：
SELECT * FROM users
LEFT JOIN user_credentials ON users.user_id = user_credential.user_id
LEFT JOIN user_level ON users.users_level_id = users_level.users_level_id以此类推。
Stack Overflow用户
发布于 2012-04-13 06:06:10
这是一个简单的查询，它将加入所有的查询。
select *
from users
left join users_credentials
on users_credentials.users_id = users.users_id
left join users_level
on users_level.users_level_id = users.users_level_id
left join users_status
on users_status.users_status_id = users.users_status_id编辑
如果您想从不同的表中获取数据，请使用
select users.* , users_credentials.* , users_level.* , users_status.*
from users
left join users_credentials
on users_credentials.users_id = users.users_id
left join users_level
on users_level.users_level_id = users.users_level_id
left join users_status
on users_status.users_status_id = users.users_status_id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/10135970
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号