After SQL Help

This is probably a simple problem for all you SQL punters but I'm no so strong so here it is:

I have a 'users' table. I also have a 'friendship' table which dictates which users are friends with other users.

If I want to retrieve all 'friends' of a particular 'user' that is straight forward. But how would I write a query to retrieve all the 'friends' of all the 'friends' of a user.

It's quite simple using multiple hits to the DB but can it be contained in a single SQL statement or SP?

You can if you use SqlServer 2005 or Oracle.

In SqlServer 2005 you can use the new (or old standard) CTE syntax like so:

declare @.uidintset @.uid = 1;with AllFriends(Id,Name)as(select u1.userid, u1.namefrom users u1join friends f1on u1.userid = f1.friendidwhere f1.userid = @.uidunionallselect u2.userid, u2.namefrom users u2join friends f2on u2.userid = f2.friendidjoin AllFriends aon f2.userid = a.id)select *from AllFriends

|||

Yeah I'm using SQL Server 2005...

OK thanks for that I'll try to adapt it to what I'm doing.

|||

I'm sorry but I just don't understand what you've done here...

I'm not even sure what to search for in terms of a tutorial to help me understand what you've done...

|||

uhm.. sorry for that...
I'm kinda bad at explaining things, so here's a tutorial:
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.aspx
hope this makes things more clear

|||Thanks mate.