4

Actually, I have 2 tables the friend table and the users table what I try to achieve is to retrieve my mutual friend by checking the friend of another user and get the data of these mutual friends from the user's table

table friend is built like this

id | user1 | user2 | friend_status

then the table data looks like this

1 | 1 | 2 | 1
2 | 1 | 3 | 1
3 | 2 | 3 | 1
4 | 1 | 4 | 1
5 | 2 | 4 | 1

Then let's say that I am the user with id 2, then in that table, I have 3 friends - 1, 3 and 4. What I want to retrieve are the common friends with user 1 that have also 3 friends - 2, 3 and 4 and retrieve the data from the table users for the 2 common mutual friend 3 and 4

funnydman
  • 9,083
  • 4
  • 40
  • 55
Mireille28
  • 337
  • 5
  • 13

3 Answers3

1

Here's a way using union all to combine all friends of user 1 and user 2 and using count(distinct src) > 1 to only select those that are friends with both users.

select friend from (
    select 2 src, user1 friend from friends where user2 = 2
    union all select 2, user2 from friends where user1 = 2
    union all select 1, user1 from friends where user2 = 1
    union all select 1, user2 from friends where user1 = 1
) t group by friend
having count(distinct src) > 1
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
1

You can use a UNION to get a users friends:

SELECT User2 UserId FROM friends WHERE User1 = 1
  UNION 
SELECT User1 UserId FROM friends WHERE User2 = 1

Then, joining two of these UNION for two different Users on UserId you can get the common friends:

SELECT UserAFriends.UserId FROM
(
  SELECT User2 UserId FROM friends WHERE User1 = 1
    UNION 
  SELECT User1 UserId FROM friends WHERE User2 = 1
) AS UserAFriends
JOIN  
(
  SELECT User2 UserId FROM friends WHERE User1 = 2
    UNION 
  SELECT User1 UserId FROM friends WHERE User2 = 2
) AS UserBFriends 
ON  UserAFriends.UserId = UserBFriends.UserId
xpy
  • 5,481
  • 3
  • 29
  • 48
  • thanks xpy but where do i get info of mutual friend from users table in that query ? – Mireille28 Mar 20 '16 at 04:37
  • This query returns the id's of the mutual friends, you can fetch the info by joining the results of this query with your `users` table. – xpy Mar 20 '16 at 09:26
0

You need something like this?

create table #table (id int, user1 int , user2 int, friend_status int)

insert into #table values

(1 , 1 , 2 , 1),
(2 , 1 , 3 , 1),
(3 , 2 , 3 , 1),
(4 , 1 , 4 , 1),
(5 , 2 , 4 , 1),
(6 , 2 , 1 , 1),
(7,  3 , 7 , 1)

select *from #table

select t1.user1, t1.user2 as friend
from #table t1
inner join
#table  t2
on (t1.user2 = t2.user2
and t1.user1 <> t2.user1)
where t1.user1<>2
order by t1.user1
bmsqldev
  • 2,627
  • 10
  • 31
  • 65