将该查询与子查询连接成一个查询

发布于 2024-11-29 05:55:14 字数 546 浏览 0 评论 0原文

有些东西就像带有类别、群组和帖子的社区。我需要选择与用户相关的所有帖子。这些关系是我所属的任何群组中发布的所有帖子都是需要的。我通过带有子查询的查询来完成它。

现在，我想以某种方式优化它，这样我只有一个没有子查询的查询。唯一的问题是，我不知道如何。此时，我正在寻求帮助。

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

WHERE `groups`.`id` IN (SELECT `group_id` FROM `group_members` WHERE `user_id` = '33') /* My sub-query. ID '33' is variable.*/
AND `posts`.`post_id` = 0 /* For only first level posts. */

如果您需要表结构，请询问。非常感谢您的建议！

原文

There are something like communities with categories, groups and posts. I need to select all posts that are related to user. These relationships are that all posts that are made in any group where I am a member are needed. I accomplish it with query with sub-query.

Now, I want to somehow optimize it so I have only one query without sub-query. Only problem, I'm not sure how. At this point, I'm looking for a help.

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

WHERE `groups`.`id` IN (SELECT `group_id` FROM `group_members` WHERE `user_id` = '33') /* My sub-query. ID '33' is variable.*/
AND `posts`.`post_id` = 0 /* For only first level posts. */

If you need table structure, just ask. Huge thanks in advice!

分享到QQ

分享到微博

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

发布评论

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

旧时浪漫 2024-12-06 05:55:14

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

JOIN `group_members`
ON (`groups`.`id` = `group_members`.`group_id`)

WHERE `group_members`.`user_id` = '33'
AND `posts`.`post_id` = 0 /* For only first level posts. */

应该可以解决问题。
至少，它应该相当于问题中的查询——如果一致，我不知道。

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

JOIN `group_members`
ON (`groups`.`id` = `group_members`.`group_id`)

WHERE `group_members`.`user_id` = '33'
AND `posts`.`post_id` = 0 /* For only first level posts. */

should do the trick.
At least, it should be equivalent to the query in the question - if it is consistent, I don't know.

回复收藏 0 原文

我纯我任性 2024-12-06 05:55:14

试试这个

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

inner join `group_members` on (`groups`.`id` = `group_members`.`group_id`)
Where `group_members`.`user_id` = '33' AND `posts`.`post_id` = 0

Try this

SELECT ... /* Lot of stuff. */
FROM `posts`

JOIN `groups`
ON (`groups`.`id` = `posts`.`group_id`)

JOIN `users`
ON (`users`.`id` = `posts`.`user_id`)

inner join `group_members` on (`groups`.`id` = `group_members`.`group_id`)
Where `group_members`.`user_id` = '33' AND `posts`.`post_id` = 0

回复收藏 0 原文

~没有更多了~