INNER JOIN, where ** each row ** must match a WHERE clause?

Question

Here is 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 correspond to the where clause. Sort of:

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 will get the corresponding row from A, and I only need a row from A, if

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

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

B is basically a filter table.

Answer 1

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

Answer 2

:

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

.

, ( EXISTS, ), :

FORALL table WHERE condition1 : condition2

( : FORALL , 1, 2 )

, :

SELECT *
FROM   a
WHERE  FORALL b WHERE b.a_id = a.id : b.value > 2

( , forall , b a.id)

, , :

SELECT *
FROM   a
WHERE  NOT EXISTS b WHERE b.a_id = a.id : NOT (b.value > 2)

SQL :

SELECT *
FROM a
WHERE NOT EXISTS (SELECT * 
                  FROM   b 
                  WHERE  b.a_id = a.id
                  AND    (b.value > 2) IS NOT TRUE)          

.

Answer 3

SELECT * FROM A 
LEFT JOIN B ON B.a_id = A.id
WHERE B.value > 2 OR B.a_id IS NULL

Answer 4

SELECT * FROM A LEFT JOIN B ON b.a_id = a.id
WHERE B.a_id IS NULL OR NOT EXIST (
        SELECT  1
        FROM    b
        WHERE  b.value <= 2) 

Answer 5

(, , ):

A, B B.a_id = A.id B.value > 2.

:

A, B B.a_id = A.id B.value > 2.

SELECT a.*  --  "rows from A" (so don't include other columns)
FROM   a
LEFT   JOIN b ON b.a_id = a.id
             AND (b.value > 2) IS NOT TRUE -- safe inversion of logic
WHERE  b.a_id IS NULL;

a WHERE NULL. IS NOT TRUE , WHERE . (b.value <= 2 OR b.value IS NULL), , ( ).

• ,

Answer 6

SELECT a.is, a.name, c.id as B_id, c.value from A 
INNER JOIN (Select b.id, b.a_id, b.value from B WHERE B.value > 2) C
on C.a_id = A.id 

, select * . , . b.Id refernces, , , . , 100% , , , . a_id .

Answer 7

EXISTS, .

SELECT A.* 
FROM 
    A 
    LEFT JOIN B ON 
        B.a_id = A.id 
        AND B.value <= 2 -- note: condition reversed!!
WHERE B.id IS NULL

B. , , WHERE .