[MySQL] Mutual/Friends in Common

How would a query for this be built? (thought this would be cool)

I have friends on my stored in tables like:
user_id, friend_id, time, status (being friends or top friends)

So with that how would one select data for display which friends you have in common with the persons profile you are visiting? I haven't really tested anything alike at the moment but will very shortly. Also a little insight would help alot with how to do this all.

 

something like this:
select friend_id from uf_table where user_id=my_id and freind_id in
(select friend_id from uf_table where user_id=persons_profile_you_are_visiting);

 

Had a "brain fart" and didn't think that what you posted would work but it turned out pretty swell and worked perfectly!

Effectively though, using that and applying a limit of 4, would that basically mean that 5 queries are being executed?

vt/mt is the friends tables

	SELECT f.friend_id, f.time, u.user_id, u.username, u.user_avatar
FROM {$vt} f, users u
WHERE f.user_id = {$this->data['visitor_id']}
	AND f.friend_id IN
	(
		SELECT friend_id
		FROM {$mt} f
		WHERE user_id = {$this->data['user_id']}
		AND f.status > 0
	)
	AND f.friend_id = u.user_id
	AND f.status > 0
ORDER BY RAND()
LIMIT 4

PHP:

Thanks for your help with that.

 

Why not?
Example:

user_id fr_id 
1 1 
1 2 
1 3 
1 4 
1 5 
1 6 
1 7 
2 1 
2 3 
2 4 
2 5 
3 2 
3 4 
3 5 
3 6 
4 1 
4 2 
4 3 
5 6 
5 7 
5 1 
6 2 
7 5 

PHP:

Query:

select fr_id from friend where user_id=$my_id and fr_id in
(select fr_id from friend where user_id=$his_id);

PHP:

my_id=1, his_id=2
output: 1 3 4 5
my_id=1, his_id=3
output: 2 4 5 6
my_id=1, his_id=7
output: 5
my_id=5, his_id=6
output: -
my_id=5, his_id=1
output: 6 7 1
my_id=2, his_id=4
output: 1 3

Is it wrong?

 

BTW, you query is the same, but you just use 2 tables.

Update: Hm, sorry. I misunderstoond you. I thought you _still_ think that my query is not correct.

 

Yeah, thats the reason why at the first impression I thought it wouldn't work. lol

Sa'll good!