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 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 );
Regards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
