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