Plz help me to get Mutual Friends sql

Sajal Koley
Sajal Koley used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What defines a mutual friend - follower / following or both?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Can you please provide input rows and the expected output? With screen shot things are not clear.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Try adding a DESCRIBE of table(s) involved + likely someone can assist.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

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

Author

Commented:
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Please try this -

SELECT *
FROM 
(
	" 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'"
)k
INNER JOIN 
(
 "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' "
)r ON r.`user_id`  = k.`user_id`

Open in new window

Okay, I'm taking a second guess at what you want then.
Now guessing that by "mutual", you actually mean a list of everyone that I'm following, who is ALSO following me?

In which case this should do it:
select u.user_id
from T_Followers f1
join T_Followers f2 on f1.following_id = f2.follower_id and f1.follower_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} 

Open in new window


If that's not it, then please clarify exactly what you mean by "mutual".
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
question abandoned. Provided multiple solutions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial