how to find friends of friends in mysql

Question

Description :

I have a table of users as following

students

id ------ name     
1  ------ John    
2  ------ Sarah    
3  ------ Peter

and friends table as

Buddies

person1 ------  person2    
1       ------  2
2       ------  3

Now I want all the friends of 2 and all other my friends whose friends are not my friends.

E.g in "people you may know" we see people that are friends of our friends but are not our friends

I have successfully written the query to find all my friends but I am not sure how to find "friends of my friends" in one query

Is there a way to do it in one query ....

I am finding friends like this

 select * from `students` join `buddy_circle` on 
'$reg_no' = `person_1` and `stregno` = `person_2` or 
'$reg_no' = `person_2` and `stregno` = `person_1`

where stregno is the id of student and buddy_circle is the friends table and $regno is the id of the user

Answer 1

Maybe this? I have tested it only with your example data.

select name from students where id in (
    select p2 from buddies where p1 in (
        select p2 from buddies where p1=[serach_for_id]));

Answer 2

I guess this will work for you.
First I select Your friends in two first query.
Then I query the Buddies table (or Friends in this example) that they are friends of your friends
It covers all the situations:

/*select my friends in col2 while I'm in col1 */
SELECT p2 FROM Friends WHERE p1 = @MyID

UNION
/*select my friends in col1 while I'm in col 2 */
SELECT p1 FROM Friends WHERE P2 = @MyID

Union
/*
select my friend's friends which
my firends are in col1 and my firend's friends are in col2
*/
SELECT  p2 FROM Friends
WHERE
    p1 in (SELECT P2 FROM Friends where P1=@MyID)

union
SELECT p1 FROM Friends
WHERE
    p2 in (SELECT P2 FROM Friends where P1=@MyID)

union    
SELECT p1 FROM Friends
WHERE
    p2 in (SELECT P1 FROM Friends where P2=@MyID)

union
SELECT p2 FROM Friends
WHERE
    p1 in (SELECT P1 FROM Friends where P2=@MyID)

Answer 3

Join twice to get friends of friends:

select distinct name
from buddy_circle a
join buddy_circle b on b.p1 = a.p2
join students on id = b.p2
where a.p1 = $reg_no

Note the order of tables in the query is such that the where clause applies to the first named table and joined tables flow on from that, which gives maximum performance.

Answer 4

Try this

select * from students As s join buddy_circle As b on s.id= b.person_2 or b.person_1 = s.id Where id =2

This query should give you a list of all students what that have the same friend of 2.

If it work for you I will re-write it in a different format to make it even better