CODEWITHSUNDEEP
mysqlsql
Vijay
Vijay

Reputation: 5433

How to get both my friends & friends of friends?

I want to get friends of mine as well as friends of friends but limited to 2nd degree friends

My table structure will be something like this

user_id  friend_user_id
1            2
1            4
2            3
2            4
2            5
3            7

What i need is, if my user id is '1' then i want all my immediate friends (in this case 2&4) and user_is 2 has some friends (3,4,5) and the expected result is 2,3,4,5

How can i get that?

Upvotes: 3

Views: 194

Answers (3)

Bohemian
Bohemian

Reputation: 425033

Use a union and an inner join:

select friend_user_id from friends
where user_id = ?
union
select f2.friend_user_id from friends f1
join friends f2 on f2.user_id = f1.friend_user_id
where f1.user_id = ?

This should perform very well (much better than using a sub-select anyway).

Upvotes: 3

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

SELECT friend_user_id 
FROM table
WHERE user_id = 1 OR user_id IN (SELECT friend_user_id FROM table WHERE user_id = 1)

Upvotes: 3

Janick Bernet
Janick Bernet

Reputation: 21194

SELECT friend_user_id FROM friends WHERE user_id = 1
UNION
SELECT friend_user_id FROM friends WHERE user_id IN (SELECT friend_user_id FROM friends WHERE user_id = 1)

This will give you a set with all friends and friends of friendsof user with id 1, with duplicates removed. You can easily extend this for 3rd, 4th, etc. by nesting the queries using the IN clause.

Upvotes: 2

Related Questions