Counting Siblings (Database Query)

[Chavista]

I have a database table that lists animals in a parent/child relationship. I can display a list of all the species (children) in the cat family (parent), for example. I also figured out how to display a taxon's number of children.

Now I'd like to figure out how to display the number of siblings a taxon has. For example, if there are eight families in the order Carnivora, then the cat family (Felidae) would displayl "7," because it has seven siblings. But if there's just one species in a particular family, then it would display "0" (or no value at all), because it has no siblings.

It seems like it should be simple to do, but I haven't figured it out yet. One complication is that there's an extra taxonomic rank between family and species (genus) that I'm not currently doing much with. An order is a family's parent, and a family is an order's child. But a species is technically a family's GRANDCHILD, and a family is that species' grandparent.

Anyway, thanks for any tips.

[wonshikee]

If they're in the same table, you can do a subquery to return the count

SELECT this,that, (SELECT COUNT(*) FROM tbl WHERE parent_id = t.animal_id) as children_count FROM tbl t

Also this belongs in the MySQL forum, not here.

[Chavista]

Thanks, that works great!

I have a follow up question, but I don't know if I should post it here or start a new thread in the MySQL section. This actually seems like more of a PHP problem to me. Anyway, the moderators can move this thread to the MySQL section if they want.

I'm not sure if Implemented your script perfectly. I simply found a similar script on my site and tweaked it to make it work like your script. This is what I wound up with:

$sql = "SELECT COUNT(*) AS numberofurls
  FROM gz_life
  WHERE Parent = '$MyURL'";
$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

switch ($result['numberofurls'])
{
 case 1:
 echo 'No Siblings';
 break;

 case 0:
 echo 'NADA';
 break;  

 default:
 echo 'Siblings';
 break;
}

Now I'd like to figure out how to make this skip a level. For example, imagine the following values:

(order) Artiodactyla
(family) Antilocapridae

Bovidae is Artiodactyla's child, and it has several siblings, so the script above would display "Siblings"

Now imagine the following relationship:

(family) Antilocapridae
(genus) Antilocapra
(species) (Antilocapra_americana)

This time, I want to know how many species are in a family, which means I want to IGNORE genus. In fact, Antilocapra_americana has no siblings; it's the only species in family Antilocapridae.

So does anyone know if there's a way to write a script that will essentially display the number of siblings for GRANDCHILDREN?

Thanks.