Revision 1252
Added by Aaron Marcuse-Kubitza almost 13 years ago
| mappings/verify.specimens.sql | ||
|---|---|---|
| 21 | 21 |
SELECT '# genera' AS ___; |
| 22 | 22 |
SELECT count(DISTINCT plantname) AS count |
| 23 | 23 |
FROM specimenreplicate |
| 24 |
JOIN party ON party_id = datasource_id AND organizationname = :datasource |
|
| 25 | 24 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 26 | 25 |
JOIN plantconcept USING (plantconcept_id) |
| 27 | 26 |
JOIN plantname |
| ... | ... | |
| 33 | 32 |
; |
| 34 | 33 |
|
| 35 | 34 |
SELECT '# species' AS ___; |
| 36 |
SELECT count(DISTINCT genus.plantname||','||species.plantname) AS count |
|
| 35 |
SELECT |
|
| 36 |
count(DISTINCT genus_plantname.plantname||','||species_plantname.plantname) |
|
| 37 |
AS count |
|
| 37 | 38 |
FROM specimenreplicate |
| 38 |
JOIN taxondetermination USING (taxonoccurrence_id) |
|
| 39 |
JOIN plantconcept USING (plantconcept_id) |
|
| 40 |
JOIN plantname AS genus |
|
| 41 |
ON genus.plantname_id = plantconcept.plantname_id |
|
| 42 |
AND genus.rank = 'Genus' |
|
| 43 |
JOIN plantname AS species |
|
| 44 |
ON species.plantname_id = plantconcept.plantname_id |
|
| 45 |
AND species.rank = 'Species' |
|
| 39 |
JOIN taxondetermination AS genus_taxondet |
|
| 40 |
ON genus_taxondet.taxonoccurrence_id = specimenreplicate.taxonoccurrence_id |
|
| 41 |
AND genus_taxondet.isoriginal |
|
| 42 |
JOIN plantconcept AS genus_plantconcept |
|
| 43 |
ON genus_plantconcept.plantconcept_id = genus_taxondet.plantconcept_id |
|
| 44 |
JOIN plantname AS genus_plantname |
|
| 45 |
ON genus_plantname.plantname_id = genus_plantconcept.plantname_id |
|
| 46 |
AND genus_plantname.rank = 'Genus' |
|
| 47 |
JOIN taxondetermination AS species_taxondet |
|
| 48 |
ON species_taxondet.taxonoccurrence_id = |
|
| 49 |
specimenreplicate.taxonoccurrence_id |
|
| 50 |
AND species_taxondet.isoriginal |
|
| 51 |
JOIN plantconcept AS species_plantconcept |
|
| 52 |
ON species_plantconcept.plantconcept_id = species_taxondet.plantconcept_id |
|
| 53 |
JOIN plantname AS species_plantname |
|
| 54 |
ON species_plantname.plantname_id = species_plantconcept.plantname_id |
|
| 55 |
AND species_plantname.rank = 'Species' |
|
| 46 | 56 |
WHERE datasource_id = |
| 47 | 57 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 48 |
AND taxondetermination.isoriginal |
|
| 49 | 58 |
; |
Also available in: Unified diff
mappings/verify.specimens.sql: # species test: Fixed to join separately on taxondeterminations for genus and species. # genera test: Removed no longer needed join on party.