Revision 7852
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 4628 | 4628 |
-- |
| 4629 | 4629 |
|
| 4630 | 4630 |
CREATE VIEW tnrs_input_name AS |
| 4631 |
WITH accepted_name AS (SELECT DISTINCT "MatchedTaxon"."acceptedScientificName" AS taxonomicname FROM ("TNRS"."MatchedTaxon" LEFT JOIN "TNRS"."AcceptedTaxon" ON (("AcceptedTaxon"."acceptedScientificName" = "MatchedTaxon"."acceptedScientificName"))) WHERE (("MatchedTaxon"."acceptedScientificName" IS NOT NULL) AND ("AcceptedTaxon"."acceptedScientificName" IS NULL))) SELECT accepted_name.taxonomicname FROM accepted_name UNION ALL (SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN "TNRS".tnrs ON ((tnrs."Name_submitted" = taxonlabel.taxonomicname))) WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND (taxonlabel.matched_label_id IS NULL)) AND (tnrs."Name_submitted" IS NULL)) EXCEPT SELECT accepted_name.taxonomicname FROM accepted_name);
|
|
| 4631 |
WITH accepted_name AS (SELECT DISTINCT "MatchedTaxon"."acceptedScientificName" AS taxonomicname FROM ("TNRS"."MatchedTaxon" LEFT JOIN "TNRS"."AcceptedTaxon" ON (("AcceptedTaxon"."acceptedScientificName" = "MatchedTaxon"."acceptedScientificName"))) WHERE (("MatchedTaxon"."acceptedScientificName" IS NOT NULL) AND ("AcceptedTaxon"."acceptedScientificName" IS NULL))) SELECT accepted_name.taxonomicname FROM accepted_name UNION ALL (SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname))) WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND (taxonlabel.matched_label_id IS NULL)) AND ("MatchedTaxon"."concatenatedScientificName" IS NULL)) EXCEPT SELECT accepted_name.taxonomicname FROM accepted_name);
|
|
| 4632 | 4632 |
|
| 4633 | 4633 |
|
| 4634 | 4634 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: tnrs_input_name: taxonlabel.taxonomicname subquery: Use new MatchedTaxon view, which use VegCore column names where possible