SQL JOIN Record

Question

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

 person: <PK> int person_id varchar[45] name friends: <PK> int person_id <PK> int friend_id 

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

I can do this easily using the 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 do not know how to write JOIN instructions. Can someone help me write an equivalent compound?

Clearly, this is a contrived example, but I tried with my real data and conceptually did not understand anything. 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 two tables together using p.person_id = f.person_id

If a person has no friends, you will not get any rows back - if you do not want this, use LEFT JOIN and you will get one row with NULL friend_id .

Edit: if you want to join friends again:

 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 

You may need a 3-way connection similar to this for your application, but as a rule, you only need 2 paths 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 people who are familiar with person_id 1 (but not with person_id 1)

Answer 2

 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 3

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

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 sure Tony Andrews got it right, except that I believe 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 are 1 .... everyone who is a friend [1] will exist in the filtered faces table when they are joined together and bounded by friends.person_id=[1]