Re: Urgent help please

In article <3f41c994$1_at_news.comindico.com.au>, "Alan" <alanpltse_at_yahoo.com.au> writes:

> 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

Drop NoOfChildren since it violates normalization.

> 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

plus:

  6 Candy 25
  7 Bobby 35

Both are over 20 and have no children.

Without subselects that would be:

  SELECT f1.name, f1.age
  FROM fred f1
  LEFT JOIN fred f2 ON f2.parent = f1.id AND f2.age <= 20   WHERE f1.age > 20
    AND f2.id IS NULL
  GROUP BY f1.id Received on Tue Aug 19 2003 - 11:31:56 CEST