Mysql 多表带条件选择

发布于 2025-01-07 16:59:53 字数 349 浏览 0 评论 0原文

我有一个菜鸟问题，但对我来说却是一个麻烦的问题。我在三个表上使用 SELECT，其中中间一个是关系表（保存关系 - 用户 ID 与地点 ID），第一个是用户表，最后一个是地点表。我已经编写了这个完美的查询

$query = "SELECT users.Username,usrxplc.User,places.Name
          FROM users,usrxplc,places
          WHERE usrxplc.Place=places.ID AND usrxplc.User=users.ID"

，它可以吐出与所有用户关联的所有位置。很好，但我想仅限于特定用户。看似简单，但我被困住了。

原文

I have a noob question but rather a troublesome one for me. I am using SELECT on three tables the middle one of which is realtional (Holds relations - ID of user against ID of Place), the first is a table of users, the last of places. I have written this perfectly woking query

$query = "SELECT users.Username,usrxplc.User,places.Name
          FROM users,usrxplc,places
          WHERE usrxplc.Place=places.ID AND usrxplc.User=users.ID"

That spits out all places associated with all users. Fine, but I would like to limit it only to a certain user. Seems simple, but I am stuck.

分享到QQ

分享到微博

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

发布评论

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

緦唸λ蓇 2025-01-14 16:59:53

您使用 WHERE子句来过滤结果，因此只需为 users.ID 添加一个子句：

select users.Username,
    usrxplc.User,
    places.name
from users,
    usrxplc,
    places
where usrxplc.Place = places.ID
    and usrxplc.User = users.ID
    and users.ID = 123

You use a WHERE clause to filter the results, so just add a clause for users.ID:

select users.Username,
    usrxplc.User,
    places.name
from users,
    usrxplc,
    places
where usrxplc.Place = places.ID
    and usrxplc.User = users.ID
    and users.ID = 123

回复收藏 0 原文

隐诗 2025-01-14 16:59:53

只是觉得需要发布替代方案 - 您可以使用 INNER JOIN 将一个表连接到另一个表，而不是选择所有表

SELECT 
    users.Username,
    places.Name

FROM users
INNER JOIN usrxplc ON usrxplc.User=users.ID
INNER JOIN places ON places.ID = usrxplc.Place

WHERE users.ID = 111

它在功能上与其他答案相同，但是当您进入更复杂的查询和表时，您会发现使用JOIN 允许更大的优化，因为您可以进一步限制每个单独 JOIN 获取的行，例如以下内容也是有效的，其中在连接到其他表之前限制用户

SELECT 
    users.Username,
    places.Name

FROM places
INNER JOIN usrxplc ON usrxplc.Place = places.ID
INNER JOIN users ON users.ID = usrxplc.User AND users.ID = 111

行在更复杂的查询中，或者如果这些表是更大，这将在一般来说，反过来会提供更优化的查询

Just felt the need to post the alternative - instead of selecting and all tables you can use INNER JOIN to join one table onto another

SELECT 
    users.Username,
    places.Name

FROM users
INNER JOIN usrxplc ON usrxplc.User=users.ID
INNER JOIN places ON places.ID = usrxplc.Place

WHERE users.ID = 111

It's functionally the same as the other answer, however when you get onto more complex queries and tables you will find that using JOINs allows for greater optimisation as you are able to further limit the rows each individual JOIN gets, for example the following is also valid, where the User row is limited before joining onto other tables

SELECT 
    users.Username,
    places.Name

FROM places
INNER JOIN usrxplc ON usrxplc.Place = places.ID
INNER JOIN users ON users.ID = usrxplc.User AND users.ID = 111

In more complicated queries, or if these tables were to be far larger, this would in turn offer a more optimal query generally speaking

回复收藏 0 原文

~没有更多了~