I was wondering what kind of SQL statement would be needed for a site like http://www.friendster.com .

The site lets you create a profile and maintain a buddylist of others on the site which is easy to do.

But with this site, you have something called a "personal network" which is a listing of the friends of people in your buddylist and all the friends of friends of friends of the friends in your buddylist.

What would be the SQL statement needed to create such a list?

For example if I had the tables:

userBuddyList
userBuddyListId | userId | buddyId
1 1 2
2 2 3
3 2 4
4 5 6

user
userId | username
1 user1
2 user2
3 user3
4 user4
5 user5
6 user6

So if I was user1 and my buddy list consisted of user2. How would I be able to list all of user2's buddys and their buddys and their buddys and their buddys etc..

I apologive for the messy formating of the sql tables, I was able to maintain the spaces when I post..

Any help on this would be much appreciated.

connect by prior clause sql -- using the tree structure. Same example that emp table uses for employee - manager relationship.