SQL row selection and child exclusion

Question

I have two tables A and B. A is the parent of B. I am trying to find all As that do not have a specific B as a child. I would usually do

SELECT A.id FROM A 
    WHERE A.id NOT IN 
        (SELECT B.AId FROM B WHERE B.someFK = foo);

However, for performance reasons, I try not to use internal selection. I tried something like:

SELECT A.id FROM A 
    LEFT JOIN B ON (A.id = B.AId)
    WHERE B.someFK! = Foo OR B.someFK IS NULL

The problem is that it returns as having more than one child, regardless of whether they have the specified B.

EDIT: Changed B.id to B.someFK

Answer 1

, :

SELECT A.id FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = foo and B.AId=a.id);

, in ( ) .

, exists - imho. .

Answer 2

LEFT JOIN A - B ( A B), , - , A B, .

, , .
, , EXISTS:

SELECT A.id
FROM A 
WHERE NOT EXISTS (SELECT * FROM B WHERE B.AId = A.id AND B.someFK = foo);

Answer 3

Try using the MAX constraint in the parent table. I have not used MySql after a while, but this should give you an idea

SELECT A.id, MAX(B.AId) FROM A 
    LEFT JOIN B ON (A.id = B.AId)
    WHERE B.id != foo OR B.id IS NULL
GROUP BY A.id

Answer 4

try it

SELECT DISTINCT A.id FROM A 
LEFT JOIN B ON (A.id = B.AId)
WHERE B.id != foo OR B.id IS NULL

Answer 5

You must put all the criteria in a LEFT JOIN.

As for repeating lines, just DISABLE it.

SELECT DISTINCT A.id 
FROM A 
LEFT JOIN B 
ON A.id = B.AId AND B.someFK = "foo"
WHERE B.AId IS NULL