CODEWITHSUNDEEP
sql
Steve
Steve

Reputation: 203

Selecting records in SQL based on another table's contents

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?

Upvotes: 9

Views: 42782

Answers (5)

Wim
Wim

Reputation: 12092

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.

Upvotes: 3

Ken Kinder
Ken Kinder

Reputation: 13170

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.

Upvotes: 13

BQ.
BQ.

Reputation: 9423

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

Upvotes: 3

Gary McGill
Gary McGill

Reputation: 27556

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

Upvotes: 15

TLiebe
TLiebe

Reputation: 7996

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>

Upvotes: 4

Related Questions