Revision 12546
Added by Aaron Marcuse-Kubitza over 10 years ago
vegbien.sql | ||
---|---|---|
5981 | 5981 |
CREATE VIEW _plots_05_list_of_plot_codes_by_project AS |
5982 | 5982 |
SELECT p.projectname AS project_name, |
5983 | 5983 |
l.authorlocationcode AS plot_code |
5984 |
FROM (((public.location l
|
|
5984 |
FROM (((public.plot l
|
|
5985 | 5985 |
JOIN public.locationevent le ON ((l.location_id = le.location_id))) |
5986 | 5986 |
JOIN public.project p ON ((p.project_id = le.project_id))) |
5987 | 5987 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
... | ... | |
6022 | 6022 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6023 | 6023 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6024 | 6024 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6025 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6025 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6026 | 6026 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6027 | 6027 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
6028 | 6028 |
WHERE (s.shortname = ("current_schema"())::text) |
... | ... | |
6053 | 6053 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6054 | 6054 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6055 | 6055 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6056 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6056 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6057 | 6057 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6058 | 6058 |
JOIN public.aggregateoccurrence ao ON ((o.taxonoccurrence_id = ao.taxonoccurrence_id))) |
6059 | 6059 |
JOIN public.plantobservation po ON ((po.aggregateoccurrence_id = ao.aggregateoccurrence_id))) |
... | ... | |
6084 | 6084 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6085 | 6085 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6086 | 6086 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6087 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6087 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6088 | 6088 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6089 | 6089 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
6090 | 6090 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
... | ... | |
6116 | 6116 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6117 | 6117 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6118 | 6118 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6119 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6119 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6120 | 6120 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6121 | 6121 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
6122 | 6122 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
... | ... | |
6146 | 6146 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6147 | 6147 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6148 | 6148 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6149 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6149 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6150 | 6150 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6151 | 6151 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
6152 | 6152 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
... | ... | |
6178 | 6178 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6179 | 6179 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6180 | 6180 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6181 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6181 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6182 | 6182 |
JOIN public.taxonoccurrence o ON ((sub_locationevent.locationevent_id = o.locationevent_id))) |
6183 | 6183 |
JOIN public.taxondetermination td ON ((td.taxonoccurrence_id = o.taxonoccurrence_id))) |
6184 | 6184 |
JOIN public.taxonverbatim tv ON ((td.taxonverbatim_id = tv.taxonverbatim_id))) |
... | ... | |
6213 | 6213 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6214 | 6214 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6215 | 6215 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6216 |
JOIN public.location l ON ((le.location_id = l.location_id)))
|
|
6216 |
JOIN public.plot l ON ((le.location_id = l.location_id)))
|
|
6217 | 6217 |
WHERE (s.shortname = ("current_schema"())::text) |
6218 | 6218 |
GROUP BY p.projectname, l.authorlocationcode |
6219 | 6219 |
ORDER BY p.projectname, l.authorlocationcode; |
... | ... | |
6231 | 6231 |
JOIN public.source s ON ((p.source_id = s.source_id))) |
6232 | 6232 |
JOIN public.locationevent le ON ((p.project_id = le.project_id))) |
6233 | 6233 |
JOIN public.locationevent sub_locationevent ON ((sub_locationevent.parent_id = le.locationevent_id))) |
6234 |
JOIN public.location l ON ((sub_locationevent.location_id = l.location_id)))
|
|
6234 |
JOIN public.plot l ON ((sub_locationevent.location_id = l.location_id)))
|
|
6235 | 6235 |
WHERE (s.shortname = ("current_schema"())::text) |
6236 | 6236 |
ORDER BY p.projectname, l.authorlocationcode; |
6237 | 6237 |
|
Also available in: Unified diff
fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries