Changeset 31407 for branches/czw_branch/20110406/ippTools/share/laptool_exposures.sql

Timestamp:

Apr 28, 2011, 6:41:17 PM (15 years ago)

Author:

watersc1

Message:

Majority of ippTools/SQL work should be complete now. Need to start queueing exposures, and testing the script

File:

• branches/czw_branch/20110406/ippTools/share/laptool_exposures.sql (modified) (2 diffs)

branches/czw_branch/20110406/ippTools/share/laptool_exposures.sql

@@ -4 +4 @@
       W.*,IFNULL(diff1.diff_id,diff2.diff_id) AS diff_id FROM (
     SELECT DISTINCT
-        lap_id,tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, labRun.label,
+        lap_id,lapRun.tess_id,projection_cell,filter,lapRun.state as lapRun_state, lapRun.registered, lapRun.fault, lapRun.label,
         lapExp.exp_id,lapExp.chip_id,lapExp.pair_id,private,pairwise,active,lapExp.data_state,
         chipRun.state as chipRun_state, sum(chipProcessedImfile.fault) as chip_faults, sum(chipProcessedImfile.quality) as chip_quality,
-        camRun.state as camRun_state,   sum(camProcessedExp.fault) AS cam_faults, sum(camProcessedExp.quality) AS cam_quality,
-        fakeRun.state as fakeRun_state, sum(fakeProcessedImfile.fault) as fake_faults,
-        warpRun.state as warpRun_state, sum(warpSkyfile.fault) as warp_faults, sum(warpSkyfile.quality) as warp_quality,
+        camRun.cam_id, camRun.state as camRun_state,   sum(camProcessedExp.fault) AS cam_faults, sum(camProcessedExp.quality) AS cam_quality,
+        fakeRun.fake_id, fakeRun.state as fakeRun_state, sum(fakeProcessedImfile.fault) as fake_faults,
+        warpRun.warp_id, warpRun.state as warpRun_state, sum(warpSkyfile.fault) as warp_faults, sum(warpSkyfile.quality) as warp_quality,
         warpRun.magicked
     FROM lapRun JOIN lapExp USING(lap_id)
-    JOIN chipRun USING(chip_id,exp_id)
-    FROM chipRun JOIN chipProcessedImfile USING(chip_id)
+    LEFT JOIN chipRun USING(chip_id)
+    LEFT JOIN chipProcessedImfile USING(chip_id)
     LEFT JOIN camRun USING(chip_id) LEFT JOIN camProcessedExp USING(cam_id)
     LEFT JOIN fakeRun USING(cam_id) LEFT JOIN fakeProcessedImfile USING(fake_id)
     LEFT JOIN warpRun USING(fake_id) LEFT JOIN warpSkyfile USING(warp_id)
-    @WHERE@
+    WHERE    @WHERE@
     AND warpSkyfile.quality != 8007 -- known cases where quality != 0, but everything's fine.
     AND warpSkyfile.quality != 3006 -- known cases where quality != 0, but everything's fine.
-
+    GROUP BY lap_id,exp_id
     ) AS W
 -- This was unreasonably slow in testing, so that's why I'm using a subquery here.
@@ -27 +27 @@
 ) AS D
 LEFT JOIN diffRun USING(diff_id)
-JOIN lapExp AS others ON (D.chip_id = lapExp.chip_id AND D.lap_id != lapExp.lap_id)
-GROUP BY chip_id
+JOIN lapExp AS others ON (D.chip_id = others.chip_id AND D.lap_id != others.lap_id)
+GROUP BY lap_id,exp_id