CODEWITHSUNDEEP
mysql
Jesy
Jesy

Reputation: 11

Find friend of my friend mysql

I have the following tables:

users(id, name) 
friend(friend1,friend2)
images(name, id)
imageliked(imageId,likedId)

I want to get the images liked by friend of my friend with two select. Can anyone help me.....

Upvotes: -3

Views: 88

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SELECT DISTINCT 
  i.*
FROM friend AS f 
INNER JOIN imageLiked AS il ON il.likedId IN(f.friend1, f.friend2)
INNER JOIN images     AS  i ON il.imageId = i.id
WHERE 2 IN(f.friend1, f.friend2); -- 2 is my id for example

Note that: This will include those images liked by you, if you want to remove them, just add AND il.likedId <> yourId to the WHERE clause.

Update:

To get the images liked by friends of you and by friends of friends:

SELECT DISTINCT 
  i.*
FROM 
(
  SELECT f1.friend1 as friend
  FROM friend AS f1
  INNER JOIN
  (
    SELECT friend1
    FROM friend
    WHERE friend2 = 2
    UNION ALL
    SELECT friend2
    FROM friend
    WHERE friend1 = 2
  ) AS f2 ON f2.friend1 IN (f1.friend1, f1.friend2)
  UNION 
  SELECT f1.friend2
  FROM friend AS f1
  INNER JOIN
  (

    SELECT friend1
    FROM friend
    WHERE friend2 = 2
    UNION ALL
    SELECT friend2
    FROM friend
    WHERE friend1 = 2
  ) AS f2 ON f2.friend1 IN (f1.friend1, f1.friend2)
)AS f 
INNER JOIN imageLiked AS il ON il.likedId = f.friend
INNER JOIN images     AS  i ON il.imageId = i.id
WHERE il.likedId <> 2;

To get only images liked by friends of my friends:

SELECT DISTINCT 
  i.*
FROM 
(
  SELECT f1.friend2 AS friend
  FROM friend AS f1
  INNER JOIN
  (

    SELECT friend1
    FROM friend
    WHERE friend2 = 2
    UNION ALL
    SELECT friend2
    FROM friend
    WHERE friend1 = 2
  ) AS f2 ON f2.friend1 IN (f1.friend1, f1.friend2)
)AS f 
INNER JOIN imageLiked AS il ON il.likedId = f.friend
INNER JOIN images     AS  i ON il.imageId = i.id
WHERE il.likedId <> 2;

Upvotes: 1

Related Questions