11

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

sgriffinusa
  • 4,203
  • 1
  • 25
  • 26
Sam
  • 133
  • 1
  • 5

6 Answers6

7

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)

Explanation

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Just tested, shouldn't need the GROUP BY. Good solution (although op says "all related B's" which means there's probably a WHERE clause for b.some_id = a.some_id in the exists). – rfusca Jul 26 '10 at 19:41
  • This is also probably the most generalized answer, because that HAVING clause could easily be extended. IE. HAVING a.val = ALL(array_agg(b.some_val)) or the like. – rfusca Jul 26 '10 at 19:49
  • @OMG: Could you explain what's going on in your answer a little bit? The use of EXISTS to mean "for all" is blowing my mind. – Justin K Jul 26 '10 at 19:53
  • @Justin K: Part 1 - 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. – OMG Ponies Jul 26 '10 at 20:06
  • @Justin K: Part 2: 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. – OMG Ponies Jul 26 '10 at 20:11
  • This answer is from almost 13 years ago, I've been developing apps for 20+ years, and I am just now learning this trick. Thank you!!!!! – danielricecodes Jun 21 '23 at 21:25
3
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)
Adam Musch
  • 13,286
  • 2
  • 28
  • 32
-1

The following should work:

SELECT *
FROM a
JOIN b ON a.key = b.key AND a.value > b.value

Because this does an inner join and not a outer join, records from A will only be included if they have records in B that satisfy the condition.

I don't use PostGRE, so I can't guarantee that the syntax is exactly correct.

sgriffinusa
  • 4,203
  • 1
  • 25
  • 26
-1

You are wanting an INNER JOIN:

SELECT
    A.*
FROM
    A
INNER JOIN B
    ON A.identifier = B.identifier
WHERE
    B.some_val > value

You will want to ensure that there is a foreign key from A to B, or some other common identifier.

Jordan S. Jones
  • 13,703
  • 5
  • 44
  • 49
-1
select * from a where a.key = b.a_key where b.value > condition
Jason
  • 1
-1

Use bookstores and books as an example.

Bookstore

bookstoreID, bookID

Book

bookID, price

I suppose you want to return all the bookstores in which all the books have a price greater than X.

select *
from Bookstore bs1
where bs1.bookstoreID not exist
 (
  select bs.bookstoreID
  from Bookstore bs, Book b
  where bs.bookID= b.bookID
  b.price < x;   -- your value                                                     
 )
Ryan Liang
  • 658
  • 2
  • 9
  • 17