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 
AND   
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  AND 
So - at least one of each.

-- 
   Richard Huxton
   Archonet Ltd