Friend Connection Query

From: ensnare (ensnare_at_gmail.com)
Date: 06/30/04 

Date: 29 Jun 2004 20:21:10 -0700

Hey everyone --

I currently have a table friends:

member_id (int 10),
connect_id (int 10),
active (char 1)

which contains friend relationships. When active is set to 1, the
relationship is confirmed. When it is set to 0, by default, it is not
yet confirmed.

For example:

member_id connect_id active
1 2 1 // 1 and 2 are friends
2 3 1 // 1 and 3 are friends
3 1 0 // 3 and 1 are not yet
friends.

I'm looking for an SQL query that would return a member and his
friends 3 levels deep. For example:

1 <-> 2 <-> 3

What would this query look like? What is the most efficient way to
write it? How would it change if it were 4 levels deep?

Now, to make things more complicated, I also have a table, members,
which basically looks like:

member_id (int 10),
username (varchar 20).

How could I also join the members table onto the query so I can get a
detailed result, such as:

ensnare <-> michael <-> tyra

Thanks much for your help! Have a great day.

Relevant Pages

  • $Lindas Freebs and Special Offers Newsletter$
    ... I verify it you will be entered to WIN a $25 Wal-Mart Gift Card ... For Things That Are Free And We Send Them Out To Our Members. ... Open a real money poker account with $50, and they give you the $50 ... Your friends will in turn ...
    (misc.consumers)
  • Re: When is a Quaker a Quaker?
    ... of the Society of Friends is more likely to be intimately familiar ... with Quaker matters than a nonmember. ... authority of USA RSOF members) is easily refuted by the ... printed listings of Quaker meetings and churches, ...
    (soc.religion.quaker)
  • BlindPassion Lifestyle Would Like To Cordially Invite You To Become A Member
    ... The neighborhood for those who like to flirt, date, and play with their friends. ... Unlike any other website BlindPassion Lifestyle has five proactive features that actively assist our members to connect with others for just a day, a night, or for a lifetime. ... As a VIP Member you will have the option of limiting all your interactions to only other VIP members within this Gated Community. ...
    (uk.people.polyamorous)
  • Re: When is a Quaker a Quaker?
    ... member of the Society of Friends is not a Quaker. ... members of an evangelical Protestant church do not deserve to be ... The historical Quaker position does not commit this fallacy. ... members of Baltimore Yearly Meeting? ...
    (soc.religion.quaker)
  • Lindas Freebie Newsletter ;) Special Offers
    ... Get 250,000+ Opt-In Subscribers FRE*E! ... Your friends will in turn ... return emails a week or less from other members who join above you! ... FREE**Kitchen Makeover Promotion (ONLY EMAIL NEEDED FOR CONTEST) ...
    (misc.consumers)