Re: Urgent help please

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
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

Original text of this message