Changeset 15512

Timestamp:

Nov 8, 2007, 11:29:30 AM (19 years ago)

Author:

jhoblitt

Message:

correctly handle warp_id's that map to multiple skycells

File:

• trunk/ippTools/share/magictool_find_complete_warpruns.sql (modified) (1 diff)

trunk/ippTools/share/magictool_find_complete_warpruns.sql

@@ -6 +6 @@
     skycell_id,
     tess_id
-FROM (
-    SELECT 
-        warpSkyCellMap.warp_id,
-        warpSkyCellMap.skycell_id,
-        warpSkyCellMap.tess_id,
-        warpSkyfile.warp_id as w2,
-        warpSkyfile.skycell_id as sc2,
-        warpSkyfile.tess_id as t2,
-        warpSkyfile.fault
-    FROM warpRun
-    JOIN warpSkyCellMap
-        USING(warp_id)
-    LEFT JOIN warpSkyfile
-        USING(warp_id, skycell_id, tess_id)
-    WHERE
-        warpRun.state = 'stop'
-    GROUP BY
-        warpRun.warp_id
-    HAVING
-        SUM(warpSkyfile.fault) = 0
-        AND COUNT(warpSkyCellMap.warp_id) = COUNT(warpSkyfile.warp_id)
-        AND COUNT(warpSkyCellMap.skycell_id) = COUNT(warpSkyfile.skycell_id)
-        AND COUNT(warpSkyCellMap.tess_id) = COUNT(warpSkyfile.tess_id)
-) as Foo
+FROM warpSkyCellMap
+JOIN
+    (SELECT warp_id FROM
+        (SELECT 
+                warpSkyCellMap.warp_id,
+                warpSkyCellMap.skycell_id,
+                warpSkyCellMap.tess_id,
+                warpSkyfile.warp_id as w2,
+                warpSkyfile.skycell_id as sc2,
+                warpSkyfile.tess_id as t2,
+                warpSkyfile.fault
+            FROM warpRun
+            JOIN warpSkyCellMap
+                USING(warp_id)
+            LEFT JOIN warpSkyfile
+                USING(warp_id, skycell_id, tess_id)
+            WHERE
+                warpRun.state = 'stop'
+            GROUP BY
+                warpRun.warp_id
+            HAVING
+                SUM(warpSkyfile.fault) = 0
+                AND COUNT(warpSkyCellMap.warp_id) = COUNT(warpSkyfile.warp_id)
+                AND COUNT(warpSkyCellMap.skycell_id) = COUNT(warpSkyfile.skycell_id)
+                AND COUNT(warpSkyCellMap.tess_id) = COUNT(warpSkyfile.tess_id)
+        ) as Foo
+    )  as Bar
+USING (warp_id)