I have a table contains parent and children information,
this is not the actual table that I am working with but has similar
structure:

ID Name Parent NoOfChildren Age
1 John NULL 3 50
2 Mary 1 0 20
3 Peter 1 0 19
4 David 1 0 30
5 Alan NULL 1 60
6 Candy 5 0 25
7 Bobby NULL 0 35
8 Vincent NULL 0 18


John, age of 50, is the parent of 3 children, Mary, Peter and David.
Alan, age of 60 has 1 child, Candy.
Boby has no children.
Vincent has no children.


I am using MySQL so does not support subquery.
How do I get the names of the parent when satifiy one of the following
conditions:
1) is over 20 years old and have no children
2) is over 20 years old and if his children who are ALL over 20 years old.

Result of the above table:
ID Name Age
5 Alan 60
7 Bobby 35

John is not included because he has child Peter who is 19.
Vincent is a parent who is only 18 so also excluded.

"Harald Fuchs" <nospam (AT) sap (DOT) com> wrote

violate the definition of a functional dependency, nor does it violate
normal forms. A relational expression value and a tuple attribute value
both will be functionally dependent on the key and nothing but the key.

A table constraint would probably be beneficial in ensuring consistency
between the derived value resulting from the relational expression and the
tuple value, but this is an integrity issue, not a normalization issue.

Regards,

Daniel Guntermann

Candy is not included before she is a child of Alan.
I only want to include the top level parent.

"Alan" <alanpltse (AT) yahoo (DOT) com.au> wrote

select
x1.name, x1.age
from
x x1, x c
where
x1.age > 20 and x1.parent is null
group by
x1.name, x1.age
having
count(case when c.parent = x1.id and c.age < 20 then 1 else null
end) = 0


if "CASE WHEN ..." is not supported by mysql, you could perhaps use
"IF ..."? I've seen "IF ..." in Q/A for mysql, so I guess "CASE ..."
is not supported?