I've just been working with the INDEX_TAXON_GROUP table and was hoping someone could give me a sanity check on the following.

Here's broadly what I am attempting to do:

Given a taxon name (e.g., 'Chiroptera') I want a list of all taxon_list_item_keys that are descendants of that 'parent' taxon no matter what list they reside on. So if you take my example of Chiroptera, I would want a list of all of the taxon_list_item_keys for all bats in the system regardless of what list those bat species are found on.

The INDEX_TAXON_GROUP table is used for traversing the taxonomic hierarchy: for any given taxon_list_item_key it lists every other taxon_list_item_key both above and below it in the hierarchy.

But things get tricky because some taxa do not have a parent taxon, or they have an irregular hierarchy. In the case of bats, for instance, there are several that reside on the "Red Data Book Vertegrates: Ireland"; these bats have a direct parent of "Mammals (Mammalia)". That being the case, a direct query on INDEX_TAXON_GROUP against the taxon_list_item_key for Chiroptera will not find these bats because they do not have Chiroptera as an ancestor.

So, to solve this - that is, to find every bat on every list, even if it isn't under a 'Chiroptera' parent - I need to find all bats beneath Chiroptera on the preferred list, then, for each of those taxa, I need to look for taxa that join to them via recommended_taxon_list_item_key; i.e., I need to look for all of the non-preferred names for each and every bat taxon.

Clear as mud? Told you I needed a sanity check. ;)

Here's the query I wrote:

select distinct
    itn1.recommended_taxon_list_item_key
    ,itn1.preferred_name
    ,itn2.taxon_list_item_key
    ,itn2.common_name
    ,itn2.preferred_name
    ,itn2.sort_order
from
    index_taxon_group itg
inner join
    index_taxon_name itn1 on
    itg.contained_list_item_key = itn1.taxon_list_item_key
inner join
    index_taxon_name itn2 on
    itn1.taxon_list_item_key = itn2.recommended_taxon_list_item_key
where
    itg.taxon_list_item_key = ( select top 1 itn1.recommended_taxon_list_item_key
                                from index_taxon_name itn1
                                where itn1.preferred_name = 'chiroptera' )
order by
    itn2.sort_order

Can anyone confirm that it does what I think it is supposed to?