Search code examples
postgresqlsequelize.js

Get all elements which have both values grouped by ParentId

I have the following problem:

I have a relation table like this:

ParentId | ValueId
1          1   
1          2
2          3
2          4
2          1

Then, I want to get the ParentId who have exactly the values which query say, no more, no less.

I have this query actually:

SELECT "ParentId" FROM public."ParentValueRelation"
WHERE "ValueId" = 1 AND "ValueId" = 2
GROUP BY "ParentId"

Expected to receive 1 but getting null

Answer in sequelize could be great but not necessary

Solution

  • There are number of ways to do this in Postgres. Like this for instance:

    SELECT "ParentId" FROM public."ParentValueRelation"
WHERE "ValueId" = 1 OR "ValueId" = 2
GROUP BY "ParentId"
HAVING COUNT("ValueID")=2

    If there are duplicates in the table, you need to replace the having clause with

    HAVING COUNT(DISTINCT "ValueID")=2