我的 MySQL JOIN 查询仅从一张表获取数据

发布于 2024-11-27 10:23:22 字数 777 浏览 0 评论 0原文

SELECT `users`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

这就是我的疑问。我的数据位于 users、users_profiles、albums 和 users_sites 中。目前，我正在四个单独的查询中获取必要的数据，但我想使用 JOIN 进行优化。

我的问题是返回的结果集仅包含 users 表中的一行数据（其中 id 为 4）。这是可以预料的，但我也希望其他表在最后加入JOIN。

例如，users_profiles 包含我已确认将 user_id 设置为 4 的行。但是 users_profiles 中的任何行都不会添加到查询中，我所看到的都是来自用户的内容。

有什么想法吗？其他表的情况也是如此 - 我已经确认它们应该被正确选择，但我不确定我做错了什么。

感谢您的帮助！

原文

SELECT `users`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

That's my query. I have data in users, users_profiles, albums, and users_sites. Currently I'm getting the necessary data in four separate queries but I want to optimise with JOINs.

My problem is that the result set being returned only contains one row of data from the users table (where the id is 4). This is to be expected but I want other tables JOINed on the end too.

For example, users_profiles contains a row that I have confirmed contains user_id set to 4. But no rows from users_profiles is ever added to the query, all I see is what comes from users.

Any ideas? It's the same case for the other tables - I've confirmed they should be selected properly but I'm not sure what I'm doing wrong.

Thanks for your help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你曾走过我的故事 2024-12-04 10:23:22

尝试：

SELECT `users`.*, `users_profiles`.*, `albums`.*, `users_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

您的代码仅从用户表中选择列。

另外，

SELECT *
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

请注意，不建议选择 *，因为它尚未优化。只需选择您需要的列即可。

Try:

SELECT `users`.*, `users_profiles`.*, `albums`.*, `users_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

Your code only selecting the columns from users table.

Alternatively,

SELECT *
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

Please beware that selecting * is not recommended as it is not optimized. Just select the column that you need.

回复收藏 0 原文

末が日狂欢 2024-12-04 10:23:22

SELECT `users`.*, `user_profiles`.*, `albums`.*, `user_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

SELECT `users`.*, `user_profiles`.*, `albums`.*, `user_sites`.*
FROM (`users`)
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

回复收藏 0 原文

-黛色若梦 2024-12-04 10:23:22

您只需从用户表中选择列；

SELECT users.*

更改您的 SELECT 语句以包含您要检索的列，以及所有表中的所有列 b>（受您的查询影响）；

SELECT * FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

包含 users_profile 和 users 表中的所有内容；

SELECT `users`.*, `users_profiles`.* FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

You only select the columns from your users table;

SELECT users.*

Change your SELECT statement to include the columns you want to retrieve, to include all columns from all tables (affected by your query);

SELECT * FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

To include everything from the users_profile and users tables;

SELECT `users`.*, `users_profiles`.* FROM `users`
JOIN `users_profiles` ON `users_profiles`.`user_id` = `users`.`id`
JOIN `albums` ON `albums`.`user_id` = `users`.`id`
JOIN `users_sites` ON `users_sites`.`user_id` = `users`.`id`
WHERE `users`.`id` = 4

回复收藏 0 原文

~没有更多了~