根据另一个表的内容在 SQL 中选择记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1703297/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting records in SQL based on another table's contents
提问by Steve
I'm a bit new to SQL and have trouble constructing a select statement. I have two tables:
我对 SQL 有点陌生，并且在构建 select 语句时遇到了麻烦。我有两个表：
Table users
int id
varchar name
Table properties
int userID
int property
and I want all user records which have a certain property. Is there a way to get them in one SQL call or do I need to first get all userIDs from the properties table and then select each user individually?
我想要所有具有特定属性的用户记录。有没有办法在一个 SQL 调用中获取它们，或者我是否需要首先从属性表中获取所有用户 ID，然后分别选择每个用户？
回答by Gary McGill
Use a JOIN:
使用JOIN：
SELECT U.id, U.name, P.property FROM users U
INNER JOIN properties P ON P.userID = U.id
WHERE property = 3
回答by Ken Kinder
If there's only one property row per user you want to select on, I think this is what you want:
如果您要选择的每个用户只有一个属性行，我认为这就是您想要的：
select
users.*
from
users,
properties
where
users.id = properties.userID
and properties.property = (whatnot);
If you have multiple property rows matching "whatnot" and you only want one, depending your database system, you either want a left join or a distinct clause.
如果您有多个与“whatnot”匹配的属性行，而您只需要一个，取决于您的数据库系统，您要么需要左连接，要么需要不同的子句。
回答by TLiebe
回答by Wim Hollebrandse
SELECT [Name] FROM Users u
JOIN Properties p on p.UserID=u.ID
WHERE p.Property=1Obviously it depends what flavour of RDBMS and TSQL you are using.
显然，这取决于您使用的 RDBMS 和 TSQL 的风格。
回答by BQ.
You're looking to JOINtables.
你在找JOIN桌子。
Assuming the id and userID columns have the same meaning, it's like this:
假设 id 和 userID 列具有相同的含义，它是这样的：
select u.name
from users u inner join properties p
on u.id = p.userID
where p.property = :ValueToFind
