sql query friend of friend

I know how to get mutal friends in 1 query but开发者_JAVA技巧 now comes the hard part, how can i get all my friends friends which are not allready my friends in 1 query that returns the users id's? I have looked at some of the posts here regarding this but i couldnt get them to work as i wanted.

My relationsships are two way so that if 1 and 2 are friends, there exists two rows in the relations table.

my tables with relevant info:

table_users
user_id

table_relations
rel_id
from_id
to_id

I have strugled with this for 2 days now, and i cant seem to get it to work.

Best of regards, Alexander

# Friends
SELECT to_id
  FROM table_relations
 WHERE from_id=ME;

# Friends of Friends
SELECT to_id
  FROM table_relations
 WHERE from_id IN (
       SELECT to_id
         FROM table_relations
        WHERE from_id=ME
       );

# Friends of Friends, not my friends
SELECT to_id
  FROM table_relations
 WHERE from_id IN (
         SELECT to_id
           FROM table_relations
          WHERE from_id=ME
       )
       AND
       to_id NOT IN (
         SELECT to_id
           FROM table_relations
          WHERE from_id=ME
       );

I'm sure there's a way to make this more efficient with temporary tables, but this should get you going.

Assuming you have an id of 1

/*Your friends' friends*/
SELECT r2.from_id FROM
table_relations r
INNER JOIN table_relations r2
ON r.from_id = r2.to_id
WHERE r.to_id = 1
EXCEPT /*EXCEPT is Standard but MINUS in Oracle*/
/*Your friends*/
SELECT from_id
FROM table_relations
WHERE to_id = 1

Or using an OUTER JOIN

SELECT DISTINCT r2.from_id
FROM
table_relations r
INNER JOIN table_relations r2
ON r.from_id = r2.to_id
LEFT OUTER JOIN table_relations r3 ON r3.to_id = r2.from_id AND r3.from_id=1
WHERE r.to_id = 1 AND r3.from_id is null

find all your friends, find all the friends friends. Then, use a NOT IN to find the difference.

select user_id
from
(
   select user_id from ... --select all the friends friends
) as friends_friends

where friends_friends.user_id not in
(
   select from_id from  
     from table_relations
     where MYID=to_id 
)