Link to home
Create AccountLog in
Avatar of Sajal Koley
Sajal Koley

asked on

Plz help me to get Mutual Friends sql

my Sql

Following Query = "SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `following_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` = {$user_id} AND `following_id` <> {$user_id} AND `active` = '1') AND `active` = '1' ";

Follower Query = " SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `follower_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` <> {$user_id} AND `following_id` = {$user_id} AND `active` = '1') AND `active` = '1'";

But i can not get Mutual Friends
sql.jpg
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

What defines a mutual friend - follower / following or both?
Can you please provide input rows and the expected output? With screen shot things are not clear.
Try adding a DESCRIBE of table(s) involved + likely someone can assist.
Just a guess since you haven't provided much detail, but I'm assuming in this case you're going to have TWO user_id values, and you're trying to see all the people that BOTH of them are following?

Mutual Followers (user_id1, user_id2)
select u.user_id
from T_Followers f1
join T_Followers f2 on f1.following_id = f2.following_id
join T_Users u on f1.following_id = u.user_id
where u.Active = 1 and f1.active = 1 and f1.follower_id = {$user_id1} and f2.active = 1 and f2.follower_id = {$user_id2}

Open in new window





FYI, you can simplify your initial queries as well if you start joining instead of using "in" subqueries.

Following (user_id)
select u.user_id
join T_Followers f
join T_Users u on f.following_id = u.user_id
where f.follower_id = ${user_id} and f.following_id <> ${user_id} and u.active = 1 and f.active = 1

Open in new window


Follower (user_id)
select u.user_id
join T_Followers f
join T_Users u on f.following_id = u.user_id
where f.follower_id <> ${user_id} and f.following_id = ${user_id} and u.active = 1 and f.active = 1

Open in new window

Avatar of Sajal Koley
Sajal Koley

ASKER

Sir i have a relation table and as u so on this pic. and can i get mutual friend or mutual followers ?

following code
SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `following_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` = {$user_id} AND `following_id` <> {$user_id} AND `active` = '1') AND `active` = '1' 

Open in new window


follower code
 SELECT `user_id` FROM " . T_USERS . " WHERE `user_id` IN (SELECT `follower_id` FROM " . T_FOLLOWERS . " WHERE `follower_id` <> {$user_id} AND `following_id` = {$user_id} AND `active` = '1') AND `active` = '1'

Open in new window



can i get mutual friend with a user_id and a relation table ?
sql.jpg
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
question abandoned. Provided multiple solutions.