Sub Query
Examples
Example 1 - Correlated Subquery
The following example which can be copied into notepad and saved as a useable report illustates the use of correlated subqueries. The main query (in fuchsia) selects all Surveys and each subquery (there are two in the example) works on each Survey by linking the Survey_Key from the main query (Survey.Survey_Key to the sub query SV.SurveyKey) so that the subqueries are run once for each Survey. The return from the subquery is given a column name. Note the use of isnull for each subquery, which will return a zero instead of null if the subquery finds nothing for the Survey. This query could be adapted to return other statistics about the Survey. The last FROM in the query is from the main query so XML reports has no problem with the subqueries so it is not necessary to use /**/FROM/**/.
<?xml version='1.0'
?>
<!-- LC Total Occurrences for Surveys
XML Report by Mike Weideli of Littlefield
Consultancy
http://www.lfield.co.uk
Version 2 7 September
2009
-->
<CustomReport title="LC23-Records in
Samples" menupath="LC reports"
description="Returns the number of
Taxon and Number of Biotope Occurrences for each Survey " >
<SQL>
<Where keytype="Default">
SELECT SURVEY.ITEM_NAME
,
isNUll( (SELECT
COUNT(TOCC.TAXON_OCCURRENCE_KEY)
FROM SURVEY SV
INNER JOIN SURVEY_EVENT
SE
ON SE.SURVEY_KEY= SV.SURVEY_KEY
INNER JOIN SAMPLE S
ON
S.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
INNER JOIN TAXON_OCCURRENCE
TOCC
ON TOCC.SAMPLE_KEY = S.SAMPLE_KEY
WHERE Sv.SURVEY_KEY =
SURVEY.SURVEY_KEY GROUP BY SV.ITEM_NAME) ,0) AS
COUNT_TAXON_OCCURRENCES,
ISNULL( ( SELECT
COUNT(BOCC.BIOTOPE_OCCURRENCE_KEY)
FROM SURVEY SV
INNER JOIN SURVEY_EVENT
SE
ON SE.SURVEY_KEY= SV.SURVEY_KEY
INNER JOIN SAMPLE S
ON
S.SURVEY_EVENT_KEY = SE.SURVEY_EVENT_KEY
INNER JOIN BIOTOPE_OCCURRENCE
BOCC
ON BOCC.SAMPLE_KEY = S.SAMPLE_KEY
WHERE Sv.SURVEY_KEY =
SURVEY.SURVEY_KEY GROUP BY SV.ITEM_NAME),0) AS
COUNT_BIOTOPE_OCCURRENCES
FROM SURVEY
ORDER BY
SURVEY.ITEM_NAME
</Where>
</SQL>
</CustomReport>