On Oct 10, 2007, at 12:38 PM, Richard Broersma Jr wrote:
I expect that my intuition is incorrect about the use of ALL() and
ANY(), but I found my result to
be reverse from what I expected.
Can anyone explain why <> ANY() behaves that way it does?
Here are two test case examples that do what I expect:
-- find all parent that only have boys
SELECT *
FROM Parents AS P
WHERE 'boy' = ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
-- find all parent that only have girls
SELECT *
FROM Parents AS P
WHERE 'girl' = ALL ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
Here is the example that doesn't do what I expect:
--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 says "Give me all parents for which it is true that all of their
children are not girls and all children are not boys" which will only
be true for parents with no children. Add a record to your Parents
table without any corresponding Children record(s) and you'll get a
result.
--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 );
This is correct. It reads "Give me all parents for whom any of their
children is not a girl AND any of their children is not a boy." So,
for a parent with both a boy and a girl, the boy is not a girl and
the girl is not a boy. You could replace the <> ANY with a simple IN
as then it would be "Give me all parents who have both a boy and a
girl."
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
