How create SQL query that validates friends of friends.

Question

Hello.

I have two tables.

USER user_id password

FRIEND_LIST user_id friend_id

If user 1 is friend of user 2 then in friend_list there will be 2 records:

1 2
2 1

Thats how I'm controlling friend list.

My question is how can I create an efficient query that validates if a user is a friend of a friend.

For example user 1 has in his friend list user 2. and user 3 has in his friend list user 2. So user 2 is a common friend of both 1 and 3.

Here is how friend_list table looks like:

1 2
2 1
3 2
2 3

No I want to know if user 1 has a friend that has as friend user 3.

The pseudocode is as follows:

validate(){    
valid = false
    list = get all friends from user 1 and store them in 'list'.
    for each friend in list {
       list2 = get all friends from friend
       for each friend2 in list2 {
        if friend2.user_id = 3 }
          valid = true
          break; //stop here because we have found that 3 is a friend of a friend of 1
        }
     }
    }
return valid
}

This is how it would look like in a programming language. Now I want to validate the same but just with an SQL query.

I tried this but I dont know if this is a good way to validate that.

select *
from friend_list fl1
inner join friend_list fl2 on fl1.user_id = fl2.user_id
inner join friend_list fl3 on fl2.friend_id = fl3.user_id
where fl1.user_id = 1 and fl3.friend_id = 3

Thanks in advance.

Thank you very much for your support. This is the first time I use this forum and helped me a lot.

I used the EXISTS code you posted like this.

SELECT EXISTS (
    SELECT
        *
    FROM
        friend_list AS users
    INNER JOIN
        friend_list AS friends
            ON users.friend_id = friends.user_id
    WHERE
        users.user_id = 1
        AND friends.friend_id = 3
) AS result

Answer 1

The query you already wrote is along the right lines, I think you need one less join to the friend-list table:

select distinct fl1.user_id, fl2.friend_id
from friend_list fl1
inner join friend_list fl2 on fl1.friend_id = fl2.user_id
where fl1.user_id = 1 and fl2.friend_id = 3

So '1' (fl1.user_id) is friends with 'x' (fl1.friend_id and fl2.user_id) who is friends with '3' (fl2.friend_id).

Because you have two complementary entries in friend-list for each pair of friends, the query is nice and simple. It would be a bit tougher if each pair of friends only got one row in friend-list ....

(edit: realised too many joins were happening...)
(edit: added a distinct into the select after comment conversation)

Answer 2

As you're looking to find out if there are Any instances of "a and b have a friend in common" you're better of using the EXISTS keyword.

The benefit of this over using COUNT or DISTINT is that the optimiser knows a couple of things:
1. The actual data doesn't matter
2. It can stop searching after the first hit

For example...

IF EXISTS (
    SELECT
        *
    FROM
        friend_list AS [user]
    INNER JOIN
        friend_list AS [friend]
            ON [user].friend_id = [friend].user_id
    WHERE
        [user].user_id = @user_id
        AND [friend].friend_id = @friend_of_friend_id
)
BEGIN
    RETURN 1
    -- Or whatever code you want to execute
END
ELSE
BEGIN
    RETURN 0
    -- Or whatever code you want to execute
END

Although this doesn't have "TOP 1" and uses "*", it neither actually returns multiple fields or rows. It literally just searches for the existance of a match and stops when it finds one.