SQL to get friends AND friends of friends of a user
- by Enrique
My MySQL tables structure is like this.
USER
int id
varchar username
FRIEND_LIST
int user_id
int friend_id
For each friend relationship I insert 2 records in FRIEND_LIST.
If user 1 is friend of user 2 then the next rows are inserted into FRIEND_LIST
1,2
2,1
I want to get the friends and friends of friends of an specific user.
The select should return columns a, b, c.
a: user_id
b: friend_id
c: username (username of friend_id )
If 1 is friend of 2 and 3.
2 is friend of 3, 4 and 5
3 is friend of 5,6,7
Then the query to get 1's friends and friends of friends should return:
1 2 two
1 3 three
2 1 one
2 3 three
2 4 four
2 5 five
3 1 one
3 5 five
3 6 six
3 7 seven
Can I get this rows with a single query?
