Re: Urgent help please
Date: 19 Aug 2003 06:07:25 -0700
Message-ID: <6dae7e65.0308190507.4b3e4263_at_posting.google.com>
"Alan" <alanpltse_at_yahoo.com.au> wrote in message news:<3f41c994$1_at_news.comindico.com.au>...
> 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
[...]
>
> 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.
>
I think you can transform that to:
1') is over 20 years old and have no children under 20 years old
> Result of the above table:
> ID Name Age
> 5 Alan 60
> 7 Bobby 35
>
Why is Candy not included? She's 25 and has no children. Same with David, 30 and no children. Anyhow, here is one attempt:
select
x1.name, x1.age
from
x x1, x c
where
x1.age > 20
group by
x1.name, x1.age
having
count(case when c.parent = x1.id and c.age < 20 then 1 else null end) = 0
NAME AGE
---------- -----------
Alan 60 Bobby 35 Candy 25 David 30
I wouldnt recomend this solution for a very large table though
[...]
HTH
/Lennart
-- the above email no longer works due to spam. values'lennart'||CHR(46)||'jonsson'||CHR(64)||'enlight'||CHR(46)||'net'Received on Tue Aug 19 2003 - 15:07:25 CEST