Socail Database Recent Friends of Friend's

[Snaxpac]

Hey Guys,

 

Here what my tables look like...

 

Member
member_id	name
1		John
2		Jane
3		Peter

Friend
member_id	friend_id	friend_status	date_accepted
1		2		1		2008-05-12 11:00:01
2		1		1		2008-05-12 11:00:01
2		3		1		2008-05-23 9:38:45
3		2		1		2008-05-23 9:38:45

 

friend_status means they are friend or not, so like 1 = friend, 2 = pending. When they are not friends, the row doesn't exist.

 

Let's say I'm logged in as john (1) and i want to who my recent added friend's friends are.

 

So the output would look something like this in php... "Jane Became Friends with Peter on 2008-05-23 9:38:45"

and mysql output:

 

friend_name	friends_friend_name	date_accepted
Jane		Peter			2008-05-23 9:38:45

 

I know how to do all the php, i just can't get my head around the query. I'm thinking that to get the friend of friend you need to have a query inside a query.

 

Select M.member_id, M.name

From members M, friends F

Where M.member_id = (Select F.friend_id

 

I'm thinking you also might need a join...

 

Any help or suggestions on where my query should be heading would be appreciated!

[Snaxpac]

ok i did a little modeling to fit with my db and its format... however, when another friend is introduced, i get this error.

 

#1242 - Subquery returns more than 1 row

 

Here is my q

 

SELECT m1.name AS friend, m1.member_id AS friend_id, m2.name AS fri_fri_name, m2.member_id fri_fri_id, f.accepted_date

FROM as2_members m1

JOIN as2_friends f ON m1.member_id = f.member_id

JOIN as2_members m2 ON f.friend_id = m2.member_id

WHERE f.member_id != '31'

AND f.member_id = (

SELECT member_id

FROM as2_friends

WHERE friend_id = '31'

AND friend_status =1 )

ORDER BY f.accepted_date DESC

[fenway]

You probably want

 

AND f.member_id IN (

 

instead of

 

AND f.member_id = (