Hi Matt
The following query is pinched from one of the Import Wizard stored procedures, where it gets the Order for the matched species. In this query, there is a temporary table called #Species, which has a field called Match_Key which corresponds to the taxon list item key. You'd need to change the taxon rank key it filters against if you want families though.
UPDATE S
SET [Order] = ITN.Actual_Name
FROM #Species S
JOIN Index_Taxon_Synonym ITS ON ITS.Synonym_List_Item_Key = S.Match_Key
JOIN Index_Taxon_Group ITG ON ITS.Taxon_List_Item_Key = ITG.Contained_List_Item_Key
JOIN Taxon_List_Item TLI
ON ITG.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
AND TLI.Taxon_Rank_Key = 'NBNSYS0000000012'
JOIN Index_Taxon_Name ITN
ON ITN.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
This query still uses the Index_Taxon_Synonym table. To use a Nameserver driven query, the query should look like:
UPDATE S
SET [Order] = ITN.Actual_Name
FROM #Species S
JOIN Index_Taxon_Name ITN1 ON ITN1.Taxon_List_Item_Key = S.Match_Key
JOIN Index_Taxon_Name ITN2 ON ITN2.Recommended_Taxon_List_Item_key=ITN1.Recommended_Taxon_List_Item_Key
JOIN Index_Taxon_Group ITG ON ITN2.Taxon_List_Item_Key = ITG.Contained_List_Item_Key
JOIN Taxon_List_Item TLI
ON ITG.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
AND TLI.Taxon_Rank_Key = 'NBNSYS0000000012'
JOIN Index_Taxon_Name ITN
ON ITN.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key
This query is indiscriminate with respect to the list it selects the Order (or family) from so if there are disputes then you may need a way of filtering to select the preferred taxon. Hopefully you can tweak these queries to your needs.
Best Wishes
John van Breda
Biodiverse IT