CODEWITHSUNDEEP
sqlpostgresql
Jacka
Jacka

Reputation: 2590

Return records not having relation to ANY given ID of related table

Let's say we have alerts table. alerts have many regulations through alert_regulations join table.

I need to fetch alerts which do not have any of given regulation ids.

My attempt was:

SELECT DISTINCT "alerts".* FROM "alerts"
  LEFT OUTER JOIN "alert_regulations" ON "alert_regulations"."alert_id" = "alerts"."id"
  LEFT OUTER JOIN "regulations" ON "regulations"."id" = "alert_regulations"."regulation_id"
WHERE "regulations"."id" NOT IN ($1, $2, $3, $4, $5)

or, avoiding one join:

SELECT DISTINCT "alerts".* FROM "alerts"
  LEFT OUTER JOIN "alert_regulations" ON "alert_regulations"."alert_id" = "alerts"."id"
WHERE "alert_regulations"."regulation_id" NOT IN ($1, $2, $3, $4, $5)

However, if alert has any other regulation than $1, $2, $3, $4, $5, it still gets returned.

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You are describing NOT EXISTS:

SELECT a.*
FROM alerts a
WHERE NOT EXISTS (SELECT 1
                  FROM alert_regulations ar
                  WHERE ar.alert_id = a.id AND
                        ar.regulation_id IN ($1, $2, $3, $4, $5)
                 );

I would strongly advise you to NOT use double quotes around column and table names. They just make queries harder to write and read -- and open the potential to identifiers that differ only in the upper-case versus lower-case letters. That can lead to errors.

Also, Postgres supports arrays, so you could pass in a list of values as an array.

Upvotes: 1

Related Questions