Changeset 15484

Timestamp:

Nov 6, 2007, 3:56:12 PM (19 years ago)

Author:

jhoblitt

Message:

fix it again!

File:

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

trunk/ippTools/share/magictool_find_unmagiced.sql

@@ -1 +1 @@
 -- find warp_id that have had all of their skycell's differenced but have not
 -- yet been magic'd
-SELECT DISTINCT
-    warpRun.warp_id
-FROM warpRun
--- find all of the skycells that came out of a single exposure
-JOIN warpSkyCellMap
-    USING(warp_id)
-LEFT JOIN warpSkyfile
-    USING(warp_id, skycell_id, tess_id)
--- find out which skycells have been diff'd
-LEFT JOIN diffInputSkyfile
-    USING(warp_id, skycell_id, tess_id)
-LEFT JOIN diffRun
-    USING(diff_id)
-LEFT JOIN diffSkyfile
-    USING(diff_id)
--- find out which diff'd skycells have been added to a magic run
-LEFT JOIN magicInputSkyfile
-    USING(diff_id)
-LEFT JOIN magicRun
-    USING (magic_id)
-WHERE
-    warpRun.state = 'stop'
-    AND (diffRun.state = 'stop' || diffRun.state IS NULL)
-    AND (warpSkyfile.fault = 0 || warpSkyfile.fault IS NULL)
-    AND (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL)
-    AND (diffInputSkyfile.kind = 'warped' || diffInputSkyfile.kind IS NULL)
-    AND (diffSkyfile.fault = 0 || diffSkyfile.fault IS NULL)
-    AND magicInputSkyfile.diff_id IS NULL
-GROUP BY
-    warpRun.warp_id, warpSkyCellMap.skycell_id, diffInputSkyfile.skycell_id
-HAVING COUNT(warpSkyCellMap.skycell_id) = COUNT(diffInputSkyfile.skycell_id)
+SELECT
+    warp_id
+FROM
+    (SELECT
+        warpRun.warp_id,
+        warpSkyfile.fault as f1,
+        diffSkyfile.fault as f2,
+        warpSkyCellMap.skycell_id as s1,
+        diffInputSkyfile.skycell_id as s2
+    FROM warpRun
+    -- find all of the skycells that came out of a single exposure
+    JOIN warpSkyCellMap
+        USING(warp_id)
+    LEFT JOIN warpSkyfile
+        USING(warp_id, skycell_id, tess_id)
+    -- find out which skycells have been diff'd
+    LEFT JOIN diffInputSkyfile
+        USING(warp_id, skycell_id, tess_id)
+    LEFT JOIN diffRun
+        USING(diff_id)
+    LEFT JOIN diffSkyfile
+        USING(diff_id)
+    -- find out which diff'd skycells have been added to a magic run
+    LEFT JOIN magicInputSkyfile
+        USING(diff_id)
+    LEFT JOIN magicRun
+        USING (magic_id)
+    WHERE
+        warpRun.state = 'stop'
+        AND (diffRun.state = 'stop' OR diffRun.state IS NULL)
+    --    AND (warpSkyfile.fault = 0 || warpSkyfile.fault IS NULL)
+        AND (diffInputSkyfile.template = 1 || diffInputSkyfile.template IS NULL)
+        AND (diffInputSkyfile.kind = 'warped' || diffInputSkyfile.kind IS NULL)
+    --    AND (diffSkyfile.fault = 0 || diffSkyfile.fault IS NULL)
+        AND magicInputSkyfile.diff_id IS NULL
+        AND warpSkyfile.warp_id IS NOT NULL
+        AND diffInputSkyfile.warp_id IS NOT NULL
+    GROUP BY
+        warpRun.warp_id
+    --    warpRun.warp_id, warpSkyCellMap.skycell_id, warpSkyCellMap.tess_id
+    HAVING
+        COUNT(warpSkyCellMap.skycell_id) = COUNT(diffInputSkyfile.skycell_id)
+        AND SUM(warpSkyfile.fault) = 0
+        AND SUM(diffSkyfile.fault) = 0 
+) as Foo