Synonyms

In SQL queries which start with a taxon selected from the Taxon_Dictionary or on a name entered by the user it will ususally be desireable to include all synonyms of the species selected.  The query which  will  return all possible keys from a taxon selected from the dictionary is :-

 Select    ITN2.Taxon_List_Item_Key FROM Index_Taxon_Name ITN1 INNER JOIN  Index_Taxon_Name ITN2  ON ITN1.Recommended_taxon_List_Item_Key = ITN2.Recommended_taxon_List_Item_Key WHERE  ITN1.Taxon_List_Item_key = '%s'   (Note %s is the parameter returned from the Dictionary when the 'type' attribute of the <Condition> tag is set to 'Taxon') 

 From a  species name 

Select   ITN2.Taxon_List_Item_Key FROM Index_Taxon_Name ITN1 INNER JOIN  Index_Taxon_Name ITN2  ON ITN1.Recommended_taxon_List_Item_Key = ITN2.Recommended_taxon_List_Item_Key WHERE  ITN1.Actual_Name= 'Name Entered'

The above can be furrher linked to Taxon_Determination etc. to produce a query which returns records for the  taxon selected. As issue here is that if  the user modified the Taxon_Dictionary to include additional names for a Taxa then duplicates lines will be returned.  Depending on what is required this could be resolved by

• using SELECT DISTINCT (this will not work if returning memo fields)
• changing the WHERE clause to include  AND ITN1.System_Supplied_Data = 1 AND ITN2.System_Supplied_Data - 1. This will ignore the user defined names.
• creating a temporary  table using the above queries, but with SELECT DISTINCT. Then using this temporary  table in the final query.