Richard Broersma Jr wrote:
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 );
I read this as: Find all parents such that none of their kids are
girls and none of their kids are boys. That is, ALL of their genders
are <> 'girl', AND ALL of their genders are <> 'boy'. Under the
obvious assumptions about gender, the result is of course empty -
except it's not clear to me what should happen for childless people ...
--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 );
I read this as: Find all parents such that at least one of their
kids is not a girl, and at least one of their kids is not a boy. Of
course, this could also be written with =.
- John Burger
MITRE
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
