Index: /trunk/ippTools/share/disttool_definebyquery_select.sql
===================================================================
--- /trunk/ippTools/share/disttool_definebyquery_select.sql	(revision 23363)
+++ /trunk/ippTools/share/disttool_definebyquery_select.sql	(revision 23363)
@@ -0,0 +1,120 @@
+SELECT
+    stage,
+    stage_id,
+    obs_mode,
+    clean
+FROM (
+    SELECT
+        'raw' AS stage,
+        rawExp.exp_id AS stage_id,
+        chipRun.chip_id AS chip_id,
+        rawExp.obs_mode,
+        distTarget.clean
+    FROM rawExp
+    JOIN chipRun USING(exp_id)
+    JOIN distTarget ON distTarget.obs_mode = rawExp.obs_mode AND distTarget.stage = 'raw'
+    JOIN rcInterest USING(target_id)
+    LEFT JOIN distRun ON distRun.stage = 'raw' AND distRun.stage_id = exp_id
+    WHERE 
+        chipRun.state = 'full'                  -- XXX: we need a completed chip stage magicDSRun because we
+--        AND rawExp.magicked and chipRun.magicked    -- to support other cameras need to add this optionally
+                                         -- need the mask file
+        AND distRun.dist_id IS NULL      -- no existing distRun 
+
+UNION
+    SELECT
+        chipRun.chip_id as stage_id,
+        'chip' as stage,
+        rawExp.obs_mode,
+        distTarget.clean
+    FROM chipRun
+    JOIN rawExp USING(exp_id)
+    JOIN distTarget ON rawExp.obs_mode = distTarget.obs_mode AND distTarget.stage = 'chip'
+    JOIN rcInterest USING(target_id)
+    LEFT JOIN distRun ON distTarget.stage = 'chip' AND distRun.stage_id = chipRun.chip_id
+    WHERE distTarget.state = 'enabled'
+        AND rcInterest.state = 'enabled'
+        AND (chipRun.magicked > 0 OR rawExp.magicked > 0)
+        AND ((chipRun.state = 'full') OR (distTarget.clean AND chipRun.state = 'cleaned'))
+        AND distRun.dist_id IS NULL      -- no existing distRun 
+
+UNION
+    SELECT
+        max(magic_ds_id) AS magic_ds_id,      -- This assumes that the last destreak run is the best
+        'cam' as stage,
+        camRun.cam_id as stage_id,
+        rawExp.obs_mode,
+        distTarget.clean
+    FROM magicDSRun 
+    JOIN chipRun ON chipRun.chip_id = magicDSRun.stage_id AND magicDSRun.stage = 'chip' 
+    JOIN camRun USING(chip_id)
+    JOIN rawExp USING(exp_id)
+    JOIN distTarget ON rawExp.obs_mode = distTarget.obs_mode AND distTarget.stage = 'cam'
+    JOIN rcInterest USING(target_id)
+    LEFT JOIN distRun ON distRun.stage = 'cam' AND camRun.cam_id = distRun.stage_id
+    WHERE magicDSRun.state = 'full'
+        -- replace hook %s
+    --  AND re_place > 0                 -- if re_place is 0 we need to get destreaked image from backup
+                                         -- if re_place is 1 we get it from the stageRun
+                                         -- This is really an issue for development, not real operations
+        AND ((camRun.state = 'full') OR (distTarget.clean AND camRun.state = 'cleaned'))
+        AND distRun.dist_id IS NULL      -- no existing distRun 
+    GROUP BY chipRun.chip_id
+
+UNION
+    -- we really don't require a  magicDSRun for fake stage do we?
+    SELECT
+        max(magic_ds_id) AS magic_ds_id,      -- This assumes that the last destreak run is the best
+        'fake' as stage,
+        fakeRun.fake_id as stage_id,
+        rawExp.obs_mode,
+        distTarget.clean
+    FROM magicDSRun 
+    JOIN chipRun ON chipRun.chip_id = magicDSRun.stage_id AND magicDSRun.stage = 'chip' 
+    JOIN camRun USING(chip_id)
+    JOIN fakeRun ON fakeRun.cam_id = camRun.cam_id
+    JOIN rawExp USING(exp_id)
+    JOIN distTarget ON rawExp.obs_mode = distTarget.obs_mode AND distTarget.stage = 'fake'
+    JOIN rcInterest USING(target_id)
+    LEFT JOIN distRun ON distRun.stage = 'fake' AND camRun.cam_id = distRun.stage_id
+    WHERE magicDSRun.state = 'full'
+        -- replace hook %s
+    --  AND re_place > 0                 -- if re_place is 0 we need to get destreaked image from backup
+                                         -- if re_place is 1 we get it from the stageRun
+                                         -- This is really an issue for development, not real operations
+        AND ((fakeRun.state = 'full') OR (distTarget.clean AND fakeRun.state = 'cleaned'))
+        AND distRun.dist_id IS NULL      -- no existing distRun 
+
+    GROUP BY chipRun.chip_id            -- by chip run to find the latest magicDSRun
+
+UNION
+    SELECT
+        max(magic_ds_id) AS magic_ds_id,
+        'warp' as stage,
+        warpRun.warp_id AS stage_id,
+        obs_mode,
+        distTarget.clean
+    FROM magicDSRun 
+    JOIN warpRun ON warpRun.warp_id = magicDSRun.stage_id AND magicDSRun.stage = 'warp' 
+    JOIN fakeRun USING(fake_id)
+    JOIN camRun ON fakeRun.cam_id = camRun.cam_id
+    JOIN chipRun USING(chip_id)
+    JOIN rawExp USING(exp_id)
+    JOIN distTarget USING(obs_mode, stage)
+    JOIN rcInterest USING(target_id)
+    LEFT JOIN distRun USING(stage, stage_id) 
+    WHERE magicDSRun.state = 'full'
+        -- replace hook %s
+    --  AND re_place > 0                 -- if re_place is 0 we need to get destreaked image from backup
+                                         -- if re_place is 1 we get it from the stageRun
+                                         -- This is really an issue for development, not real operations
+        AND ((warpRun.state = 'full') OR (distTarget.clean AND warpRun.state = 'cleaned'))
+        AND distRun.dist_id IS NULL      -- no existing distRun 
+    GROUP BY warpRun.warp_id
+
+
+) as foo
+
+
+
+