CODEWITHSUNDEEP
mysql
Matt Hintzke
Matt Hintzke

Reputation: 8004

MySQL query to find a slightly complex relationship

I am trying to devise a MySQL query to select a specific record based off 2 parameters. Lets say I have 3 tables. Users, Conversations, and UserConvo where UserConvo is a reference table that links the Many-to-Many relationship between a User and any Conversation that they are having. Think of it as a very basic web chat.

Now, if given an arbitrary number of UserIds (i.e. [1,2,3]), I want to find a Conversation that involves EXACTLY these Users. Therefore, for the case of UserIds 1, 2, and 3, I want to see if there exists a Conversation where only user 1, 2, and 3 are included.

Is there a purely MySQL way to do this? My first thoughts are to just query the Conversations in which UserId 1, 2 and 3 are present. Then, somehow check each record to see if all ConversationIds match, but I do not have a whole lot of MySQL experience and am not sure of its potential.

Thanks

Upvotes: 0

Views: 32

Answers (1)

sgeddes
sgeddes

Reputation: 62861

Something like this should work. It uses count with case - the idea is to compare the overall count per conversation to those where the user id in (1,2,3):

select c.id
from conversations c
  join userconversations uc on c.id = uc.conversationid
  join users u on uc.userid  = u.id
group by c.id
having count(u.id) = count(case when u.id in (1,2,3) then 1 end)

Upvotes: 1

Related Questions