Friend of Friend

Hey Guys,

I´m a newbie on SQL and I got a problem at the following thing:

I want to create a relationship between a member and the friend of his friend.

The first level (friend of member) is no problem:


SELECT m2.name
FROM member m1
INNER JOIN friends f ON friend1 = m1.id OR friend2 = m1.id
INNER JOIN member m2 ON friend1 = m2.id OR friend2 = m2.id
WHERE m1.name LIKE '%STEVE%'
AND m1.id != m2.id

The second level works, too but I do not know how to get only one table and no name double:

SELECT m2.name, m3.name
FROM member m1
INNER JOIN friends f1 ON friend1 = m1.id OR friend2 = m1.id
INNER JOIN member m2 ON friend1 = m2.id OR friend2 = m2.id
INNER JOIN friends f2 ON f2.friend1 = m2.id OR f2.friend2 = m2.id
INNER JOIN member m3 ON f2.friend1 = m3.id OR f2.friend2 = m3.id
WHERE m1.name LIKE "%Steve%"
AND m1.id != m2.id AND m2.id != m3.id

Anyone can help me with my problem?

Edit: Okay I got a solution but is there a better one?


SELECT b1.name, b2.name
FROM member b1
INNER JOIN friends f ON friend1 = b1.id OR friend2 = b1.id
INNER JOIN member b2 ON friend1 = b2.id OR friend2 = b2.id
WHERE b1.name LIKE '%Steve%'
AND b1.id != b2.id
UNION
SELECT b1.name, b3.name
FROM member b1
INNER JOIN friends f ON f.friend1 = b1.id OR f.friend2 = b1.id
INNER JOIN member b2 ON f.friend1 = b2.id OR f.friend2 = b2.id
INNER JOIN friends f2 ON f2.friend1 = b2.id OR f2.friend2 = b2.id
INNER JOIN member b3 ON f2.friend1 = b3.id OR f2.friend2 = b3.id
WHERE b1.name LIKE '%Steve%'
AND b1.id != b2.id AND b2.id != b3.id

That second solution looks okay to me (the one with the Union), but I wouldn’t use WHERE b1.name LIKE '%Steve%' but use the ID of the user instead (in Steve’s case that’s 1). Why? Suppose there also is a user called “Steven”, then your query will return all friends of Steve, all friends of friends Steve, all friends of Steven and all friends of friends of Steven. Clearly the friends and friends of friends of Steven have no business in the result set :slight_smile:

Hey Scallio,

Thanks for your answer. Of course, ID-is the better solution. :slight_smile: