Many to many query for postgresql

Question

I have an elementary many-to-many relation in my Postgresql DB

Table - User

id name ...

Table - Chat

id name

Table - chatuser

id userId chatId

And I would like to get all chats (actually, there will be only one such chat), which will have only users, defined by their ids.

For example, I have user with id 1, user with id 2 and user with id 3. How can I get chats, which contain only these users, not less and not more. That means, that chats with only someone of these users will not be included in the result. Only chats which contain exactly these users

I understand, there`s got to be such question already, but I did not manage to find it.

Gordon Linoff · Accepted Answer · 2017-04-02 15:04:41Z

0

Here is one method using group by and having:

select cu.chatid
from chatuser cu
group by cu.chatid
having sum(case when cu.userid not in (1, 2, 3) then 1 else 0 end) = 0;

This counts the number of users that are not in your list. The = 0 says that the chatId has none of them.

In Postgres, you can shorten this to:

select cu.chatid
from chatuser cu
group by cu.chatid
having sum( (cu.userid not in (1, 2, 3))::int) = 0;

answered Apr 2, 2017 at 15:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrey Over a year ago

Let`s suppose we have only 2 users in query with ids 1 and 2 to make it easier to check. Then it works strange for case, when I have two chatuser instances like {userId: 1, chatId: 1} and {userId: 2, chatId: 2}. I would like the query to return nothing in that case, but yours returns both chats.

Gordon Linoff Over a year ago

@Andrey . . . A chat with only one user meets the conditions specified in the question. If you have a different question, then ask it as another question.

Andrey Over a year ago

Possibly, I was not accurate enough. Tried to change my question according to the requirements.

Collectives™ on Stack Overflow

Many to many query for postgresql

Table - User

Table - Chat

Table - chatuser

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table - User

Table - Chat

Table - chatuser

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related