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
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);
you have to use Join if you want to fetch data from two or more tables.
Try this
select * from users left join friend on users.id_users = friend.id_user
// you also give where condition for particular user_id