通过具有相同结果的两个变量过滤sql搜索

Question

I'm not sure how I can do this but I have two tables:

--users--
id_users (index)
name_users


--friend--
id_friend (index)
id_user (connects to id_users)
linked_friend (also links to id_users as this is the id of the requested friend)

for a friendship to be requested one row exists with the user who requested in friend.id_user col and the user they are requesting being in the friend.linked_friend col. To confirm the friendship link another row is created with the info reversed so that there are two rows for each friendship with each user being in the friend.id_user AND the friend.linked_friend col. (let me know if I could do this in a better way)

so what I need to do is for each user list all users on the system that are in a confirmed friendship (two rows) and aren't themselves.

SELECT id_user, name_user FROM user WHERE id_user <> $userId

obviously removes themselves from the list but I'm at a loss as to how to select users that have both rows.

I hope that is clear enough.

Thanks

Answer 1

You could join the friend table twice to demand that rows in both direction exist:

select  u1.name
,       u2.name
from    users u1
join    friend f1
on      u1.id_users = f1.id_user
join    friend f2
on      f2.id_user = f1.linked_friend 
        and f2.linked_friend = f1.id_user
join    users u2
on      u2.is_users = f2.id_user
        and u1.id_users < u2.id_users -- Display friends once

I agree with Jack Pettinger that this design is fairly clunky. There should be only one row in the friends table for each relation. It should have a unique constraint to enforce that, like:

alter table Friends add constraint CHK_Friend check (friend1_id < friend2_id);
create index UX_Friends on Friends (friend1_id, friend2_id);