People You May Know Feature - Need Only Friends Friends
Posted: Wed Dec 28, 2011 4:48 pm
I'm in the process of making a people you may know feature. To do this I am trying to grab my friends friends that I am not friends with. I need the SQL to say... If my friends friends ID matches any of my friends Id then exclude it from the select. I'm not sure how to do this or at least how to do it efficiently.
Query:
My ID My Name My Friends Id My Friends Name My Friends Friends ID My Friends Friends Name
1 Jason 2 Chelsea 4 Davey
1 Jason 2 Chelsea 6 Jim
1 Jason 2 Chelsea 12 Peter
1 Jason 2 Chelsea 16 Cameron
1 Jason 2 Chelsea 38 Felicia
1 Jason 4 Davey 5 Adam
1 Jason 4 Davey 14 Jeffrey
1 Jason 5 Adam 6 Jim
1 Jason 5 Adam 14 Jeffrey
1 Jason 5 Adam 17 Dan
1 Jason 10 Michael 5 Adam
1 Jason 13 Jacek 4 Davey
1 Jason 20 Victor 1 Jason
Query:
SELECT users.id, users.firstname, users.lastname, myfriend.id, myfriend.firstname, myfriend.lastname, theirfriend.id, theirfriend.firstname, theirfriend.lastname FROM users INNER JOIN partners ON partners.user_id = users.id AND partners.approved = 1 INNER JOIN users myfriend ON myfriend.id = partners.friend_id INNER JOIN partners partners2 ON partners2.user_id = myfriend.id INNER JOIN users theirfriend ON theirfriend.id = partners2.friend_id WHERE users.id = 1Current Results
My ID My Name My Friends Id My Friends Name My Friends Friends ID My Friends Friends Name
1 Jason 2 Chelsea 4 Davey
1 Jason 2 Chelsea 6 Jim
1 Jason 2 Chelsea 12 Peter
1 Jason 2 Chelsea 16 Cameron
1 Jason 2 Chelsea 38 Felicia
1 Jason 4 Davey 5 Adam
1 Jason 4 Davey 14 Jeffrey
1 Jason 5 Adam 6 Jim
1 Jason 5 Adam 14 Jeffrey
1 Jason 5 Adam 17 Dan
1 Jason 10 Michael 5 Adam
1 Jason 13 Jacek 4 Davey
1 Jason 20 Victor 1 Jason