Get all the friends of friends list

Question

I have a table "friendship" like this

user_id
friend_id

For each friendship I make one record instead of two.

---------------------
user_id | friend_id |
--------------------
  1     |   2       |
--------------------

And I Don't add (2 , 1) into table. So, I need to get all the friends of friends list including those who are already in my friend list preferably without subqueries. Any suggestions ? Thanks :)

And... is there any reason for no subqueries? That's like going to MacDonalds and pretending there are no burgers. — Glenn, Sep 05 '13 at 02:13
@webarto I think reason is simple. You table size in this case is less in two times. — vazgen, Sep 05 '13 at 09:56
@Glenn I think answer of your question is here http://stackoverflow.com/questions/2577174/join-vs-subquery — vazgen, Sep 05 '13 at 09:58

score 0 · Accepted Answer · answered Sep 05 '13 at 02:03

0

Are you looking for something like this?

SELECT f2.friend_id
  FROM friendship f1 JOIN friendship f2
    ON f1.friend_id = f2.user_id 
 WHERE f1.user_id = 1

Here is SQLFiddle demo

answered Sep 05 '13 at 02:03

peterm

91,357
15
148
157

What if you want to get *friends of friends* for user 3 in your current fiddle? 3 has 2 as friend (per table design). – Dejan Marjanović Sep 05 '13 at 02:12
Sorry, but it doesn't work correct , if you will insert in your table pair like this (2,1) instead (1,2).. – vazgen Sep 05 '13 at 03:13

Get all the friends of friends list

1 Answers1