Changeset 15515 for trunk/ippTools/share/magictool_find_complete_diffed_exposures.sql

Timestamp:

Nov 8, 2007, 12:05:41 PM (19 years ago)

Author:

jhoblitt

Message:

correctly handle warp_id's that map to multiple skycells

File:

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

trunk/ippTools/share/magictool_find_complete_diffed_exposures.sql

@@ -4 +4 @@
     skycell_id,
     tess_id
-FROM warpComplete 
-LEFT JOIN diffInputSkyfile
-    USING(warp_id, skycell_id, tess_id)
-WHERE 
-    (diffInputSkyfile.template = 0 || diffInputSkyfile.template IS NULL)
-GROUP BY
-    warpComplete.warp_id
-HAVING
-    COUNT(warpComplete.warp_id) = COUNT(diffInputSkyfile.warp_id)
-    AND COUNT(warpComplete.skycell_id) = COUNT(diffInputSkyfile.skycell_id)
-    AND COUNT(warpComplete.tess_id) = COUNT(diffInputSkyfile.tess_id)
+FROM diffSkyfile
+JOIN 
+    (SELECT
+        warp_id,
+        diff_id,
+        skycell_id,
+        tess_id,
+        diffSkyfile.fault
+    FROM warpComplete 
+    LEFT JOIN diffInputSkyfile
+        USING(warp_id, skycell_id, tess_id)
+    LEFT JOIN diffSkyfile
+        USING(diff_id)
+    WHERE 
+        (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL)
+    GROUP BY
+        warpComplete.warp_id
+    HAVING
+        SUM(diffSkyfile.fault) = 0
+        AND COUNT(warpComplete.warp_id) = COUNT(diffInputSkyfile.warp_id)
+        AND COUNT(warpComplete.skycell_id) = COUNT(diffInputSkyfile.skycell_id)
+        AND COUNT(warpComplete.tess_id) = COUNT(diffInputSkyfile.tess_id)
+    ) as Foo
+USING 
+    (diff_id)