Richard Broersma Jr wrote:
Here is the example that doesn't do what I expect:
I'm guessing you're just stood too close to the problem.
--find all parents that have a mixture of boys and girls.
--but this doesn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid )
AND 'boy' <> ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
This translates to
WHERE <none of the children are girls>
AND <none of the children are boys>
Assuming you have a two-state gender then that's nothing.
For 'girl' <> ALL (...) then all the values you test must be not girls.
--This query gets what I want
--but I believe that it shouldn't return anything
SELECT *
FROM Parents AS P
WHERE 'girl' <> ANY ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid )
AND 'boy' <> ANY ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
Translates to:
WHERE <any child is not a girl> AND <any child is not a boy>
So - at least one of each.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
