1 Reply by ILawrie (edited by ILawrie 25-02-2008 09:37:31)

  • ILawrie
  • Member
  • Offline
  • Registered: 10-11-2006
  • Posts: 22

Re: Selection of groups in SQL

Is there an obvious way to filter by groups (I know this can be done in Recorder 6) using MS SQL Server Management Express?

I'm really thinking about in the WHERE clause of an SQL Query, other than by typing in a long list of species as below?

WHERE INDEX_TAXON_NAME.PREFERRED_NAME = 'Enallagma cyathigerum'
      OR INDEX_TAXON_NAME.PREFERRED_NAME = 'Pyrrhosoma nymphula'
      OR INDEX_TAXON_NAME.PREFERRED_NAME = 'Ischnura elegans'
      OR ...

      ...OR INDEX_TAXON_NAME.PREFERRED_NAME = 'Leucorrhinia dubia';

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Selection of groups in SQL

Hi

The table you need to look at is called INDEX_TAXON_GROUP. If you join from INDEX_TAXON_NAME to INDEX_TAXON_GROUP on the Taxon_List_Item_Key, then join back to INDEX_TAXON_NAME using the CONTAINED_LIST_ITEM_KEY you will get a list of all taxa in the group. This only works within the list your original taxon is selected from. A more complete query can be achieved by the following series of tables:
INDEX_TAXON_NAME
INDEX_TAXON_SYNONYM
INDEX_TAXON_GROUP
INDEX_TAXON_SYNONYM
INDEX_TAXON_NAME

If you need help writing this query then let us know.

Best Wishes

John

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by ILawrie

  • ILawrie
  • Member
  • Offline
  • Registered: 10-11-2006
  • Posts: 22

Re: Selection of groups in SQL

Hi John

Thanks for that - I'll give it a go first and see how I get on.

cheers

Iain

4 Reply by ILawrie

  • ILawrie
  • Member
  • Offline
  • Registered: 10-11-2006
  • Posts: 22

Re: Selection of groups in SQL

Hi John

Just to update that I've now implemented this and I include it as a subquery to search for specific taxon groups when I'm searching and this returns the expected result set.

Cheers

Iain