Given the below chats_users
table in Postgres 9.5, with integer columns chat_id
and user_id
, I want to find the chat_id
associated with an exact set of user_id
s, for example:
user_id IN (1, 3) => chat_id 2 user_id IN (1, 2, 3) => chat_id 1 user_id IN (1, 2) => no result
Is there a simple join to return the correct chat_id
?
|-----------------------|
| chats_users |
|-----------|-----------|
| chat_id 1 | user_id 1 |
| chat_id 1 | user_id 2 |
| chat_id 1 | user_id 3 |
| chat_id 2 | user_id 1 |
| chat_id 2 | user_id 3 |
| chat_id 3 | user_id 2 |
| chat_id 3 | user_id 3 |
|-----------------------|
Best Answer
Assuming a
PRIMARY KEY
on(chat_id, user_id)
- or aUNIQUE
constraint plusNOT NULL
on both columns.To allow any number of IDs, use a 1-dimensional array parameter:
Typically, in function or prepared statements, you provide the array parameter only once. Example for prepared statement:
Tests:
db<>fiddle here
Old sqlfiddle.
That's basically the first solution by Martin in this related answer on SO (with some modifications to fit your case):
There are faster solutions for a small, given number of IDs.