I'm a bit new to SQL and have trouble constructing 开发者_如何转开发a select statement. I have two tables:
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?
Use a JOIN
:
SELECT U.id, U.name, P.property FROM users U
INNER JOIN properties P ON P.userID = U.id
WHERE property = 3
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.
Check out the JOIN command. You could write a query like the following:
SELECT
name
FROM
users u
INNER JOIN properties p
ON u.id = p.userID
WHERE
p.property = <some value>
You're looking to JOIN
tables.
Assuming the id and userID columns have the same meaning, it's like this:
select u.name
from users u inner join properties p
on u.id = p.userID
where p.property = :ValueToFind
SELECT [Name] FROM Users u
JOIN Properties p on p.UserID=u.ID
WHERE p.Property=1
Obviously it depends what flavour of RDBMS and TSQL you are using.
精彩评论