SQL: select records in which ALL merged records satisfy some condition

Question

How to write an SQL query that returns a record only if ALL related records in the joined table satisfy some conditions.

For example, if A has many Bs, I want SELECT * FROM A WHERE, all related Bs for this A have the value B.some_val>

I know this is probably a pretty simple question, so thanks for any help. Also, if that matters, I use postgres.

Sam

Answer 1

Assuming no correlation is needed, use:

SELECT a.* FROM A a WHERE EXISTS(SELECT NULL FROM B b HAVING MIN(b.some_val) > a.val) 

If you 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) 

Description

EXISTS is evaluated on a boolean based on the first match — this makes it faster than using IN, and — unlike using JOIN — will not duplicate rows. The SELECT part does not matter - you can change it to EXISTS SELECT 1/0 ... and the query will still work, despite the obvious division by zero error.

The subquery in EXISTS uses the aggregate function MIN to get the smallest value B.some_val - if this value is greater than a.val, a.val is less than all b. The only need for a WHERE is for correlation — aggregate functions can only be used in a HAVING .

Answer 2

 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) 

Answer 3

The following should work:

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

Because it is an internal join , not an external join , entries from A will only be included if they have entries in B that satisfy the condition.

I do not use PostGRE, so I can not guarantee the correct syntax.

Answer 4

Do you want INNER JOIN:

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

You want to make sure that there is a foreign key from A to B or some other common identifier.

Answer 5

 select * from a where a.key = b.a_key where b.value > condition 

Answer 6

Use bookstores and books as an example.

Book Shop

bookstoreID, bookID

Book

bookID price

I suppose you want to return all bookstores in which all 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 )