Revision 11640
Added by Aaron Marcuse-Kubitza about 11 years ago
| vegbien.sql | ||
|---|---|---|
| 4844 | 4844 |
-- |
| 4845 | 4845 |
|
| 4846 | 4846 |
CREATE VIEW tnrs_input_name AS |
| 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));
|
|
| 4847 |
SELECT DISTINCT "MatchedTaxon".scrubbed_unique_taxon_name AS taxonomicname FROM ("TNRS"."MatchedTaxon" LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" ON (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_unique_taxon_name = "MatchedTaxon".scrubbed_unique_taxon_name))) WHERE (("MatchedTaxon".scrubbed_unique_taxon_name IS NOT NULL) AND ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_unique_taxon_name 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
bugfix: schemas/vegbien.sql: tnrs_input_name: MatchedTaxon self-join: must use a NOT NULL column for a proper anti-join. this unfortunately requires the more verbose LEFT JOIN ON syntax (which allows using the pkey as the NOT NULL column) instead of NATURAL LEFT JOIN (which requires using another column, which are all nullable)