Solved: Plz help me to get Mutual Friends sql

Create Account

PHP

Questions

Followers

Top Experts

Sajal Koley

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

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Julian Hansen🇿🇦

What defines a mutual friend - follower / following or both?

Pawan Kumar🇮🇳

Can you please provide input rows and the expected output? With screen shot things are not clear.

David Favor🇺🇸

Try adding a DESCRIBE of table(s) involved + likely someone can assist.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Snarf0001🇨🇦

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

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

Pawan Kumar🇮🇳

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

ASKER CERTIFIED SOLUTION

Snarf0001🇨🇦

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Pawan Kumar🇮🇳

question abandoned. Provided multiple solutions.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

PHP

Questions

Followers

Top Experts

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.