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
