Revision 11639
Added by Aaron Marcuse-Kubitza about 11 years ago
| vegbien.sql | ||
|---|---|---|
| 4844 | 4844 |
-- |
| 4845 | 4845 |
|
| 4846 | 4846 |
CREATE VIEW tnrs_input_name AS |
| 4847 |
WITH "taxon_scrub.scrubbed_unique_taxon_name.*" AS (SELECT DISTINCT "MatchedTaxon".scrubbed_unique_taxon_name AS taxonomicname FROM ("TNRS"."MatchedTaxon" NATURAL LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*") WHERE (("MatchedTaxon".scrubbed_unique_taxon_name IS NOT NULL) AND ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank IS NULL))) SELECT "taxon_scrub.scrubbed_unique_taxon_name.*".taxonomicname FROM "taxon_scrub.scrubbed_unique_taxon_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 ("MatchedTaxon"."concatenatedScientificName" IS NULL)) EXCEPT SELECT "taxon_scrub.scrubbed_unique_taxon_name.*".taxonomicname FROM "taxon_scrub.scrubbed_unique_taxon_name.*");
|
|
| 4847 |
SELECT DISTINCT "MatchedTaxon".scrubbed_unique_taxon_name AS taxonomicname FROM ("TNRS"."MatchedTaxon" NATURAL LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*") WHERE (("MatchedTaxon".scrubbed_unique_taxon_name IS NOT NULL) AND ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank IS NULL)) UNION SELECT DISTINCT taxonlabel.taxonomicname FROM (taxonlabel LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname))) WHERE ((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."concatenatedScientificName" IS NULL));
|
|
| 4848 | 4848 |
|
| 4849 | 4849 |
|
| 4850 | 4850 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: tnrs_input_name: use plain UNION, which automatically removes duplicates, rather than UNION ALL with a manual EXCEPT-removal of rows in the first SELECT