SQL JOIN query writing

Question

Hi,

I'm trying to write a simple query involving two tables. The "person" table has a unique person_id and a name, and the "friends" table has a person_id and a friend_id which is a FK to a person_id in the person table.

person: int person_id varchar[45] name

friends: int person_id int friend_id

I want to select the name of all of person 1's friends.

I can do this easily using an IN statement: SELECT p.name FROM person p WHERE p.person_id IN (SELECT f.friend_id FROM friends f WHERE f.person_id = 1);

However, I am not proficient at writing JOIN statements. Can somebody help me write the equivalent join?

Clearly this is a contrived example, but I have tried with my real data and am conceptually missing something. Thanks.

Answer 1

You want something like this:

SELECT p.name, f.friend_id
FROM person AS p
INNER JOIN friends AS f ON p.person_id = f.person_id
WHERE p.person_id = 1

This joins the two tables together using p.person_id = f.person_id

If a person has no friends, you won't get any rows back - if you don't want this then use LEFT JOIN and you'll get one row with a NULL friend_id.

Edit: if you want to join friends back on to person:

SELECT p.name AS person_name, friend.name AS friend_name
FROM person AS p                                         -- Our person
INNER JOIN friends AS f ON p.person_id = f.person_id     -- the join table
INNER JOIN person AS friend on f.friend_id = friend.id   -- Join back on person again
WHERE p.person_id = 1

Maybe you need a 3-way join like this for your app, but more usually you'd only need a 2-way as above, or like this:

SELECT p.name, f.friend_id
FROM person AS p
INNER JOIN friends AS f ON p.person_id = f.friend_id
WHERE f.person_id = 1

This will give you the names of all the people that are friends with person_id 1 (but not person_id 1's name)

Answer 2

SELECT p.name FROM person p 
INNER JOIN friends f ON f.friend_id = p.person_id
WHERE f.person_id = 1;

Answer 3

select 
    p.name,
    p2.name as friend_name,
from
    person p 
    inner join friends f on p.person_id = f.person_id
    inner join person p2 on f.friend_id = p2.person_id -- friends
where
    p.person_id = <your criteria>

Answer 4

select p.name
from person p, friends f
where f.friend_id = p.person_id
and f.person_id = 1

Answer 5

I'm pretty sure that Tony Andrews got it right, except that I think that the correct syntax puts the source table on the left and the joined table on the right...

SELECT p.name FROM person p INNER JOIN friends f ON p.person_id = f.friend_id WHERE f.person_id = 1

This will return the [person.name] field of all records where the [person.person_id] value is found in the [friends.friend_id] field AND the [friends.person_id] fields equals 1....everyone that is a friend of [1] will exist in the filtered person table when they're joined together and limited to friends.person_id=[1]