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
Sajal KoleyAsked:
Who is Participating?
 
Snarf0001Commented:
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".
1
 
Julian HansenCommented:
What defines a mutual friend - follower / following or both?
0
 
Pawan KumarDatabase ExpertCommented:
Can you please provide input rows and the expected output? With screen shot things are not clear.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Try adding a DESCRIBE of table(s) involved + likely someone can assist.
0
 
Snarf0001Commented:
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

0
 
Sajal KoleyAuthor 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
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
question abandoned. Provided multiple solutions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.