Selecting records in SQL based on another table's contents

Question

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?

Answer 1

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>

Answer 2

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

Answer 3

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

Answer 4

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.