I'm trying to create a query to extract a 2nd layer of "friendship" from a table.

I have a table with the followin structure.

[f_id][u_id][f_u_id]

f_id = primary key, auto increment. This is the "Friend Entry ID"
u_id = Users userID
f_u_id = Friend of User userID

Right now, the query:

Select * from friends where u_id=$u_id;

Will return all "Friends" of the user with the userid $u_id.

I want to have a query that will return a 2nd level of friendship. IE the friends of the users friends.

I could do a php loop,

Select * from friends where u_id=$u_id

for each $u_id { select * from friends where u_id = $u_id }


But that would be a LOT of database queries and would slow things down. I remember from my Database management class that there is a way to do somethign similar to this, but I can't remember how.

Any help?

aaron, you can do a self join, haven't done one i a while but i will look for my sql book and back to you if you did not find the answer. Doing a search for self joins will help u

A self join is correct.

A self join is just an inner join with a table and itself. Your query then would be:

SELECT f2.f_u_id
FROM friends AS f1
INNER JOIN friends AS f2 ON f1.f_u_id = f2.u_id
WHERE f1.u_id = $u_id


and this is a different syntax for the same thing:

SELECT f2.f_u_id
FROM friends AS f1, friends AS f2
WHERE f1.f_u_id = f2.u_id AND f1.u_id = $u_id

dupe, please delete.

One small suggestion I'd like to make is making your column names more programmer-friendly ie:

[f_id] - EntryID
[u_id] - UserID
[f_u_id] - FriendID

This way if you or someone else has to do maintenance at a later stage you won't be scratch your head trying to decrypt your column names :)

One small suggestion I'd like to make is making your column names more programmer-friendly ie:

[f_id] - EntryID
[u_id] - UserID
[f_u_id] - FriendID

This way if you or someone else has to do maintenance at a later stage you won't be scratch your head trying to decrypt your column names :)


Those aren't the correct column names, I used them as an example :)