Revision 1596
Added by Aaron Marcuse-Kubitza almost 13 years ago
| verify.specimens.sql | ||
|---|---|---|
| 34 | 34 |
; |
| 35 | 35 |
|
| 36 | 36 |
SELECT '# species' AS ___; |
| 37 |
SELECT |
|
| 38 |
count(DISTINCT genus_plantname.plantname_id |
|
| 39 |
||','||species_plantname.plantname_id) AS count |
|
| 37 |
SELECT count(DISTINCT plantname.plantname_id) AS count |
|
| 40 | 38 |
FROM specimenreplicate |
| 41 | 39 |
JOIN taxondetermination USING (taxonoccurrence_id) |
| 42 | 40 |
JOIN plantconcept USING (plantconcept_id) |
| 43 |
JOIN plantname_ancestor AS genus_ancestor |
|
| 44 |
ON genus_ancestor.plantname_id = plantconcept.plantname_id |
|
| 45 |
JOIN plantname AS genus_plantname |
|
| 46 |
ON genus_plantname.plantname_id = genus_ancestor.ancestor_id |
|
| 47 |
AND genus_plantname.rank = 'genus' |
|
| 48 |
JOIN plantname_ancestor AS species_ancestor |
|
| 49 |
ON species_ancestor.plantname_id = plantconcept.plantname_id |
|
| 50 |
JOIN plantname AS species_plantname |
|
| 51 |
ON species_plantname.plantname_id = species_ancestor.ancestor_id |
|
| 52 |
AND species_plantname.rank = 'species' |
|
| 41 |
JOIN plantname_ancestor USING (plantname_id) |
|
| 42 |
JOIN plantname |
|
| 43 |
ON plantname.plantname_id = plantname_ancestor.ancestor_id |
|
| 44 |
AND plantname.rank = 'species' |
|
| 53 | 45 |
WHERE datasource_id = |
| 54 | 46 |
(SELECT party_id FROM party WHERE organizationname = :datasource) |
| 47 |
AND taxondetermination.isoriginal |
|
| 55 | 48 |
; |
| 56 | 49 |
|
| 57 | 50 |
SELECT '# binomials' AS ___; |
Also available in: Unified diff
mappings/verify.specimens.sql: # species: Don't join at all on genus because DISTINCT is on the plantname_id rather than the plantname, which is already unique for a given genus because plantname_unique includes parent_id