Sql query each other

Question

I know how to get mutal friends in 1 query, but now the hard part comes, how can I get all the friends of my friends who are not my friends in 1 query, which returns user IDs? I looked at some of the posts here regarding this, but I could not get them to work the way I wanted.

My relationship is two ways, so if 1 and 2 are friends, there are two rows in the relationship table.

my tables with relevant information:

table_users
user_id

---

table_relations
rel_id
from_id
to_id

I have been struggling with this for 2 days, and I cannot get it to work.

Sincerely, Alexander

Answer 1

Assuming you have idfrom 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 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

Answer 2

# 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
       );

, , .

Answer 3

Find all your friends, find all friends of friends. Then use NOT INto 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 
)