Re: SQL Query - Too Many Rows - Duplication
Hi I'm writing a query in MS SMS to select all taxon occurrences and I'm hopeful that I'm pretty much there. Then there is reality, D'oh. I'm getting around 30% more rows out than I have in the summary info and this also ties in with the total number reported within the reporting wizard with no filters on. So I'm not excluding unchecked etc. which is fine at present.
I have included "WHERE (dbo.TAXON_DETERMINATION.PREFERRED = 1)" so should not be getting duplicated from non preferred taxa, but still have too many rows.
Does anyone know what is wrong with this?
SELECT dbo.INDEX_TAXON_NAME.PREFERRED_NAME AS Latin_Preffered, dbo.INDEX_TAXON_NAME.COMMON_NAME,
dbo.TAXON_DETERMINATION.PREFERRED, dbo.SAMPLE.SAMPLE_REFERENCE, dbo.SAMPLE.SPATIAL_REF,
dbo.FormatEventRecorders(dbo.SAMPLE.SAMPLE_KEY) AS Recorders, dbo.LCReturnDate(dbo.SAMPLE.VAGUE_DATE_START,
dbo.SAMPLE.VAGUE_DATE_TYPE, 'F') AS Start_Date, dbo.LCReturnDate(dbo.SAMPLE.VAGUE_DATE_END, dbo.SAMPLE.VAGUE_DATE_TYPE, 'F')
AS End_Date, dbo.INDEX_TAXON_NAME.SORT_ORDER, dbo.INDEX_TAXON_NAME.ABBREVIATION, dbo.SURVEY.ITEM_NAME AS Survey_Name,
dbo.LOCATION.FILE_CODE, dbo.LOCATION.DESCRIPTION, dbo.ufn_GetDesignations(dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY, 1,
'SYSTEM0000000000', NULL) AS All_Designated, dbo.LCReturnEastingsV2(dbo.SAMPLE.SPATIAL_REF, 'OSGB', 0) AS Easting,
dbo.LCReturnNorthingsV2(dbo.SAMPLE.SPATIAL_REF, 'OSGB', 0) AS Northing, dbo.TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY
FROM dbo.TAXON_LIST_ITEM INNER JOIN
dbo.TAXON_DETERMINATION ON
dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY INNER JOIN
dbo.TAXON_OCCURRENCE ON
dbo.TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY = dbo.TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY INNER JOIN
dbo.SAMPLE ON dbo.TAXON_OCCURRENCE.SAMPLE_KEY = dbo.SAMPLE.SAMPLE_KEY INNER JOIN
dbo.TAXON_COMMON_NAME ON dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.TAXON_COMMON_NAME.TAXON_LIST_ITEM_KEY INNER JOIN
dbo.INDEX_TAXON_NAME ON
dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY INNER JOIN
dbo.SURVEY_EVENT ON dbo.SAMPLE.SURVEY_EVENT_KEY = dbo.SURVEY_EVENT.SURVEY_EVENT_KEY INNER JOIN
dbo.SURVEY ON dbo.SURVEY_EVENT.SURVEY_KEY = dbo.SURVEY.SURVEY_KEY INNER JOIN
dbo.LOCATION ON dbo.SAMPLE.LOCATION_KEY = dbo.LOCATION.LOCATION_KEY AND
dbo.SURVEY_EVENT.LOCATION_KEY = dbo.LOCATION.LOCATION_KEY INNER JOIN
dbo.TAXON_DESIGNATION ON dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.TAXON_DESIGNATION.TAXON_LIST_ITEM_KEY INNER JOIN
dbo.Index_Taxon_Designation ON dbo.TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = dbo.Index_Taxon_Designation.Taxon_List_Item_Key INNER JOIN
dbo.TAXON_DESIGNATION_TYPE ON
dbo.TAXON_DESIGNATION.TAXON_DESIGNATION_TYPE_KEY = dbo.TAXON_DESIGNATION_TYPE.TAXON_DESIGNATION_TYPE_KEY AND
dbo.Index_Taxon_Designation.Taxon_Designation_Type_Key = dbo.TAXON_DESIGNATION_TYPE.TAXON_DESIGNATION_TYPE_KEY
WHERE (dbo.TAXON_DETERMINATION.PREFERRED = 1)
Any pointers would be most appreciated.
Tony
Somerset Environmental Records Centre