Displaying grand children in parent-child table

PHP
1

I have a database table that lists animals in a parent-child relationship, like this:

TAXON | PARENT
Mammalia | Vertebrata
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae
Canis_lupus | Canis

The query below displays a particular taxon’s children. For example, on a family page (e.g. Canidae), it will display that family’s genera (e.g. Canis). And if I display it on the genus page (Canis), it displays that genus’ children, or species (e.g. Canis_lupus).


$Children = mysql_query ("SELECT L.N, L.Taxon, L.Parent, L.Rank
 FROM gz_life AS L
 WHERE L.Parent = '$MyURL'");

I’d like to know if there’s a way to skip a generation, so that displaying the query on a family page would display that family’s GRAND CHILDREN, or species.

TAXON | PARENT
Mammalia | Vertebrata
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae (Skip the children)
Canis_lupus | Canis (Display the grand children instead.)

I figured out how to do the opposite - display grandparents and great grandparents. But I don’t know how to do the opposite.

Thanks.

2

Using a JOIN:


SELECT L2.N, L2.Taxon, L1.Parent, L2.Rank
FROM gz_life AS L1
INNER JOIN gz_life AS L2
ON L1.Taxon = L2.Parent
WHERE L1.Parent = '$MyURL'
3

Wow, that’s a cool trick; thanks!

(Maybe I shouldn’t call it a “trick,” but you know what I mean. ;))