[SOLVED] friends of friends query

[phorcon3]

mysql table

 

table: friends

rows: friend1, friend2

 

i want to get the friends of friend1's friends ...so when friend1 = 'test' how do i get test's friends' friends? god i hope that makes sense lol..

 

i do know that i could first fetch test's friends ..and then loop those through another query ..but i wanna do that all within just ONE single query ...any ideas?

 

select friend2 from friends where friend1 = test blahblah (blahblah)

[gevans]

I'm imagining your table does something like this;

 

user_id - the id of the user

friend1 - one of his friends

friend2 - another friend

 

in which case something along the lines of...

 

SELECT `friend1`, `friend2`
FROM `friends`
WHERE `user_id` IN(SELECT `user_id`
FROM `friends`
WHERE `friend1` = 'test');

[phorcon3]

mysql_table (friends)

 

id      friend1      friend2
1       test1        test2
2       test2        test1
3       test2        test3
4       test3        test2
5       test2        test4
6       test4        test2

 

now i want test1's friends' friends, which in this case would output, test3 and test4

 

i can only think of doing it this way:

 

$a=mysql_query("SELECT friends2 FROM friends WHERE friend1 = test1);

while($b=mysql_fetch_assoc($a))

{

$c=mysql_query("SELECT friends2 FROM friends WHERE friend1 = $b[friend2]");

while($d=mysql_fetch_assoc($c))

{

$d.=['friend2'];//outputs test3 and test4

}

}

 

but i wanna do all that within just one single query ...i dont know if thats possible or not.. i hope it makes more sense now :/

[gevans]

this seems to work;

 

SELECT `f1`.`friend2` FROM `friends` AS f1 JOIN `friends` AS f2
ON `f2`.`friend2` = `f1`.`friend1`
WHERE `f2`.`friend1`='test1'

[phorcon3]

geesh, thanks a lot;) appreciate your help