Re: <> ALL( ... ) and <> ANY ( ... ) didn't behave as expected

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