How can I write an SQL query that returns a record only if ALL of the associated records in a joined table satisfy some condition.
For example, if A has many B, I want to SELECT * FROM A WHERE all related B's for a given A have B.some_val > value
I know this is probably a pretty basic question, so thanks for any help. Also, if it makes a difference, I'm using postgres.
Sam
Assuming no need for correlation, use:
SELECT a.*
FROM A a
WHERE EXISTS(SELECT NULL
FROM B b
HAVING MIN(b.some_val) > a.val)
If you do need correlation:
SELECT a.*
FROM A a
WHERE EXISTS(SELECT NULL
FROM B b
WHERE b.id = a.id
HAVING MIN(b.some_val) > a.val)
The EXISTS
evaluates on a boolean, based on the first match - this makes it faster than say using IN, and -- unlike using a JOIN -- will not duplicate rows. The SELECT portion doesn't matter - you can change it to EXISTS SELECT 1/0 ...
and the query will still work though there's an obvious division by zero error.
The subquery within the EXISTS
uses the aggregate function MIN to get the smallest B.some_val - if that value is larger than the a.val value, the a.val is smaller than all of the b values. The only need for a WHERE
clause is for correlation - aggregate functions can only be used in the HAVING
clause.
select * from A
where -- at least one record in B is greater than some_val
exists (select null from B
where B.some_val > :value
and A.join_column = B.join_column)
and -- no records in B are not greater than some_val
not exists (select null from B
where B.some_val <= :value
and A.join_column = B.join_column)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With