query to find friends of a friend

[sinkorswim]

Hi all

I'm experimenting with writing my own social network script but I'm struggling a bit with one of the queries. I have a users table, and a friendships table containing user_id and friend_id columns which are both foreign keys linking to users.id (sql dump at end of this post).

if x is a friend of y, and the friendship is approved, then y is also a friend of x. I'm using this query to retreive all the friends of x (in this example, x has an id of 99):


SELECT
CASE
WHEN user_id=99 THEN friend_id
ELSE user_id
END
AS fid FROM friendships
WHERE (user_id=99 OR friend_id=99)
AND approved = 1;

so far so good, but what I'd like to do now is extend the search to find all friends of friends of x, but I'm not making any progress.

can anyone help?
thanks

tyler

sql dump:

create database if not exists `foaf`;
USE `foaf`;

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `friendships` */

DROP TABLE IF EXISTS `friendships`;

CREATE TABLE `friendships` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL,
`friend_id` int(11) default NULL,
`approved` tinyint(1) default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;