Question 246992 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select record based on multiple keys from joined table

I have 3 tables:
conversations
participants
contacts

participants is many to many between conversations and contacts. It has contact_id, and conversation_id.

I want to write a select query that takes one or more contact_id values and returns a record from table conversations where there are rows matching ALL the passed in fields.

For example, I have:

conversations

id
1

contacts

id
1
2

participants

id contact_id conversation_id
1 1 1
2 2 1

If I call this query with [1, 2], it should return conversation 1. If I call it with [1], it should return nothing.

How do I do that?

I have tried using a subquery, but I would need to know the conversation id in the subquery, which will execute first, without the context.

Tags:

2

Answers


  1. 0

    Can do this with a combination of SQL JOINs and GROUP BY with HAVING clause to filter the conversations that have all the specified contact_id values in the participants table

    e.g

    SELECT c.id
FROM conversations c
JOIN participants p ON c.id = p.conversation_id
WHERE p.contact_id IN (1, 2) -- replace with the list of contact_ids you want to search for
GROUP BY c.id
HAVING COUNT(DISTINCT p.contact_id) = 2; -- adjust the count to match the number of contact_ids in the list

    the above query will return the conversation(s) that have all the specified contact_ids. If you want to search for different sets of contact_ids, just adjust the IN clause and the count in the HAVING clause accordingly.

    Login or Signup to reply.
  2. 0

    From the described output, it appears that the query is intended to return conversations where all of the specified contacts, and only those contacts, are participants (which is slightly different from the stated requirement of "returns a record from table conversations where there are rows matching ALL [selected contacts]"). There are, of course, many ways to construct a query to satisfy such a request. The following query uses an array as presented in the original post:

    SELECT conversations.*
  FROM conversations
  JOIN participants
    ON conversations.id = participants.conversation_id
  LEFT JOIN UNNEST(ARRAY[1, 2]) criteria(contact_id)
    ON participants.contact_id = criteria.contact_id
  GROUP BY conversations.id
  HAVING COUNT(DISTINCT participants.contact_id) = COUNT(DISTINCT criteria.contact_id);

    Note that this query protects against redundant contact IDs in the criteria array and also determines the number of distinct contact IDs instead of requiring the count to be explicitly declared.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search