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