Project

General

Profile

« Previous | Next » 

Revision 12403

Added by Aaron Marcuse-Kubitza almost 11 years ago

bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses

View differences:

trunk/validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql
58 58 
-- ------------------
59 59 
CREATE OR REPLACE VIEW _plots_06_list_of_plots_with_stem_measurements AS
60 60 
SELECT DISTINCT p.project_name, pm."SiteCode"
61 
FROM projects p JOIN "plotMetadata" pm
62 
JOIN "plotObservations" po JOIN stems s
63 
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
64 
AND po."PlotObsID"=s.plotobs_id
61 
FROM projects p
62 
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
63 
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
64 
JOIN stems s ON po."PlotObsID"=s.plotobs_id
65 65 
GROUP BY p.project_name, "SiteCode"
66 66 
ORDER BY p.project_name, "SiteCode"
67 67 
;
......
133 133 
CREATE OR REPLACE VIEW _plots_11_count_of_stems_per_plot_in_each_project AS
134 134 
SELECT p.project_name, "SiteCode" AS plotcode,
135 135 
COUNT(DISTINCT stem_id) AS stems
136 
FROM projects p JOIN "plotMetadata" pm
137 
JOIN "plotObservations" po JOIN stems s
138 
ON p.project_id=pm.project_id AND pm."PlotID"=po."PlotID"
139 
AND po."PlotObsID"=s.plotobs_id
136 
FROM projects p
137 
JOIN "plotMetadata" pm ON p.project_id=pm.project_id
138 
JOIN "plotObservations" po ON pm."PlotID"=po."PlotID"
139 
JOIN stems s ON po."PlotObsID"=s.plotobs_id
140 140 
GROUP BY p.project_name, "SiteCode"
141 141 
ORDER BY p.project_name, "SiteCode"
142 142 
;

Also available in: Unified diff