Mercurial > gemma

diff schema/geoserver_views.sql @ 3656:2a079d0a71c1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ensure sounding results are associated to matching bottleneck version
author Tom Gottfried <tom@intevation.de>
date Thu, 13 Jun 2019 19:13:42 +0200
parents 02951a62e8c6
children db87f34805fb
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Thu Jun 13 11:20:17 2019 +0200
+++ b/schema/geoserver_views.sql	Thu Jun 13 19:13:42 2019 +0200
@@ -135,7 +135,7 @@
         LEFT JOIN fairway_availability_latest fal
             ON b.id = fal.bottleneck_id
         LEFT JOIN sounding_result_latest srl
-            ON b.id = srl.bottleneck_id
+            ON b.bottleneck_id = srl.bottleneck_id
     WHERE NOT b.erased;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
@@ -200,7 +200,7 @@
     FROM waterway.bottlenecks bn LEFT JOIN (
         SELECT bottleneck_id, max(date_info) AS current
             FROM waterway.sounding_results
-            GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
+            GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
     WHERE NOT bn.erased
     ORDER BY objnam;
 
@@ -220,4 +220,5 @@
         JOIN waterway.sounding_results srs
             ON sd.subtrahend = srs.id
         JOIN waterway.bottlenecks bn
-            ON srm.bottleneck_id = bn.id;
+            ON srm.bottleneck_id = bn.bottleneck_id
+                AND srm.bottleneck_validity = bn.validity;