CODEWITHSUNDEEP
mysql
PHPMYSQLDEV
PHPMYSQLDEV

Reputation: 73

MySQL: Retrieving ID where exactly 2 rows share the same ID but have different userIDs

I have a MySQL table that associates a user to a conversation that looks like the following image or sqlfiddle:

MySQL Table Screenshot

http://sqlfiddle.com/#!9/b4d329

As you can see, user 1000001 and user 1000002 both belong to 2 conversations - 10 and 20.

What I need to do is retrieve the conversation_id where only users 1000001 and user 1000002 are associated with it. The correct conversation_id that would be pulled is 10.

As you can see with conversation_id 20, a 3rd user is associated with it - 1000003 - so I don't want to pull that conversation_id, even though users 1000001 and 1000002 are associated with it.

Also note that the scenario can't happen when only one user_id is associated with a conversation_id.

Thank you so much for your help!

Upvotes: 1

Views: 70

Answers (3)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Here is a performant approach, using no subqueries at all. You can simply filter out results in Having clause, using conditional aggregation:

SELECT 
  conversation_id 
FROM assoc_user__conversation 
GROUP BY conversation_id 
HAVING 
  -- all the rows to exists only for 1000001 or 1000002 only
  SUM(user_id IN (1000001, 1000002)) = COUNT(*)

Result

| conversation_id |
| --------------- |
| 10              |

View on DB Fiddle

Another variation of conditional aggregation possible is: 

SELECT 
  conversation_id 
FROM assoc_user__conversation 
GROUP BY conversation_id 
HAVING 
  -- atleast one row for 1000001 to exists
  SUM(user_id = 1000001) AND  
  -- atleast one row for 1000002 to exists
  SUM(user_id = 1000002) AND  
  -- no row to exist for other user_id values
  NOT SUM(user_id NOT IN (1000001, 1000002))

Upvotes: 2

Sahil Dhoked
Sahil Dhoked

Reputation: 372

If we assume that every conversation has at least 2 participants, we can remove all conversations where someone else is there, leaving us with conversations in which only these 2 users are present.

Note: We need the above assumption because there might be cases where 1000001 is the only participant and we do not want to show those conversations.

select conversation_id
from table
where conversation_id not in (
    select conversation_id 
    from table 
    where user_id not in (1000001, 1000002))

Edit:

If it's possible that only one user_id is associated with a conversation_id, we can remove those conversations too.

select conversation_id
from table
where conversation_id not in (
    select conversation_id 
    from table 
    where user_id not in (1000001, 1000002))
group by conversation_id
having count(*) = 2

Assuming that same user_id won't be in the same conversation twice.

Upvotes: 1

Nick Ellis
Nick Ellis

Reputation: 1077

I haven't tested this code, but I'm pretty sure it is logically sound. You didn't say the name of the table so I just called it table.

select conversation_id from (
  select conversation_id, count(*) as count, t1.user_id as user_1, t2.user_id as user_2 
  from table t1
  join table t2 on (t1.conversation_id = t2.conversation_id and t1.user_id != t2.user_id)
  where t1.user_id = 1000001 and
  t2.user_id = 1000002
) as inner_table
where inner_table.count = 2

Upvotes: 0

Related Questions