[SOLVED] INNER JOIN where **every** row must match the WHERE clause?

INNER JOIN where **every** row must match the WHERE clause?

Here's a simplified example of what I'm trying to do. I have two tables, A and B.

A          B
-----      -----
id         id
name       a_id
           value

I want to select only the rows from A where ALL the values of the rows from B match a where clause. Something like:

SELECT * from A INNER JOIN B on B.a_id = A.id WHERE B.value > 2

The problem with the above query is that if ANY row from B has a value > 2 I'll get the corresponding row from A, and I only want the row from A if

1.) ALL the rows in B for B.a_id = A.id match the WHERE, OR

2.) There are no rows in B that reference A

B is basically a table of filters.

Solution

SELECT  *
FROM    a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   b.a_id = a.a_id
                AND (b.value <= 2 OR b.value IS NULL)
        )