CODEWITHSUNDEEP
mysqlsql
SoWhat
SoWhat

Reputation: 5622

Get mutual subscriptions between two users

I have a table as follows

id comm_id user_id ..

This stores user subscriptions to communities. Now I want to query this table so that only those subscriptions are fetched which are mutual between 2 users(say user_id: 2 and 9)

Give me the sql query tablename: db_community_subscribers id, community_id, user_id

"20"      1         "2"
"28",  NULL         "2"
"31"   NULL,        "2"
"43",   4           "2"
"47     1             9
"57",   NULL,       "2"
"59",   "12",      "9"
"60     14          2
"62",  NULL         2

These are the subscriptions of users 2 and 9

I want to select only 1 and 12 since these are the common subscriptions

Upvotes: 2

Views: 106

Answers (4)

Naltharial
Naltharial

Reputation: 2152

Have you tried a simple JOIN?

SELECT t1.`community_id`
FROM `tablename` t1
LEFT JOIN tablename t2 ON (t1.`community_id` = t2.`community_id`)
WHERE t1.`user_id` = 2
    AND t2.`user_id` = 9

It should, for every user_id 2's community, find a matching user 9's community.

Upvotes: 1

user359040
user359040

Reputation:

Try:

select comm_id, group_concat(id) as subscription_ids
from subscriptions
where user_id in (2,9)
group by comm_id
having count(distinct user_id)=2

Upvotes: 1

user319198
user319198

Reputation:

Try below

  SELECT group_concat(distinct comm_id)
  FROM table 
  WHERE user_id in(2, 9)
  group by user_id  having count(id) >1

OR:

  SELECT distinct comm_id
  FROM table 
  WHERE user_id in(2, 9)
  group by user_id  having count(id) >1

Please do required changes Let me know if anything else you want.

Upvotes: 1

John Woo
John Woo

Reputation: 263843

try this one:

I think this will work now:

SELECT Comm_ID
FROM tableNAme
WHERE  user_id IN (2,9)
GROUP BY Comm_ID
HAVING COUNT(Comm_ID) > 1

OR

SELECT DISTINCT d.Comm_ID
FROM
    (SELECT Comm_ID
    FROM tableNAme
    WHERE user_id in (1,2)) d
GROUP BY d.Comm_ID
HAVING COUNT(d.Comm_ID) > 1

Upvotes: 3

Related Questions