Topic: Query for complete species list
Hi
I have been trying to write a new query to get the complete species list from the database with numbers of each taxon
Using this:
SELECT INDEX_TAXON_NAME_1.PREFERRED_NAME, INDEX_TAXON_NAME_1.COMMON_NAME, COUNT(TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY)
AS CountOfOccs, TAXON_RANK.SHORT_NAME, TAXON_GROUP.TAXON_GROUP_NAME
FROM TAXON_GROUP
INNER JOIN TAXON_VERSION ON TAXON_GROUP.TAXON_GROUP_KEY = TAXON_VERSION.OUTPUT_GROUP_KEY
INNER JOIN TAXON_LIST_ITEM
INNER JOIN INDEX_TAXON_NAME ON TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY
INNER JOIN INDEX_TAXON_NAME AS INDEX_TAXON_NAME_1 ON INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME_1.TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_DETERMINATION ON TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY
INNER JOIN TAXON_OCCURRENCE ON TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY = TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY
INNER JOIN TAXON_RANK ON TAXON_LIST_ITEM.TAXON_RANK_KEY = TAXON_RANK.TAXON_RANK_KEY ON
TAXON_VERSION.TAXON_VERSION_KEY = TAXON_LIST_ITEM.TAXON_VERSION_KEY
WHERE (TAXON_DETERMINATION.PREFERRED = 1)
GROUP BY INDEX_TAXON_NAME_1.PREFERRED_NAME, INDEX_TAXON_NAME_1.COMMON_NAME, INDEX_TAXON_NAME_1.SORT_ORDER,
TAXON_RANK.SHORT_NAME, TAXON_GROUP.TAXON_GROUP_NAME
ORDER BY INDEX_TAXON_NAME_1.SORT_ORDER
gives me a shorter than expected list, but a record count nearly three times the size of the database. Any ideas what I am doing wrong?
Biological Survey Data Manager
National Trust