SELECT *
FROM (
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicRun.magic_id,
    magicRun.exp_id,
    magicDSRun.label,
    camera,
    magicMask.uri as streaks_uri,
    NULL AS inv_streaks_uri,
    stage,
    stage_id,
    class_id as component,
    rawImfile.uri as uri,
    NULL as path_base,
    magicRun.inverse,
    camProcessedExp.path_base as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM magicDSRun
JOIN magicMask USING (magic_id)
JOIN magicRun USING (magic_id)
JOIN camProcessedExp USING(cam_id)
JOIN rawImfile ON magicRun.exp_id = rawImfile.exp_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    AND magicDSFile.component = rawImfile.class_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'raw'
    AND magicDSFile.component IS NULL
UNION
  -- chipProcesedImfiles
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicDSRun.magic_id,
    chipRun.exp_id,
    magicDSRun.label,
    camera,
    magicMask.uri as streaks_uri,
    NULL AS inv_streaks_uri,
    stage,
    stage_id,
    class_id as component,
    chipProcessedImfile.uri,
    chipProcessedImfile.path_base,
    magicRun.inverse,
    camProcessedExp.path_base as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM magicDSRun
JOIN magicMask USING (magic_id)
JOIN magicRun USING(magic_id)
JOIN camProcessedExp USING(cam_id)
JOIN chipRun ON chip_id = stage_id
JOIN chipProcessedImfile USING(chip_id)
JOIN rawExp ON chipRun.exp_id = rawExp.exp_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    AND magicDSFile.component = chipProcessedImfile.class_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'chip'
    AND chipRun.state = 'full'
    AND chipProcessedImfile.fault = 0
    AND chipProcessedImfile.quality = 0
    AND magicDSFile.component IS NULL
UNION
  -- camProcessedExp
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicDSRun.magic_id,
    chipRun.exp_id,
    magicDSRun.label,
    camera,
    magicMask.uri as streaks_uri,
    NULL AS inv_streaks_uri,
    stage,
    stage_id,
    'exposure' as component,
    NULL AS uri,
    camProcessedExp.path_base,
    magicRun.inverse,
    camProcessedExp.path_base as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM magicDSRun
JOIN magicMask USING (magic_id)
JOIN magicRun USING(magic_id)
JOIN camRun ON magicDSRun.stage_id = camRun.cam_id
JOIN camProcessedExp ON camRun.cam_id = camProcessedExp.cam_id
JOIN chipRun USING(chip_id)
JOIN rawExp ON chipRun.exp_id = rawExp.exp_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'camera'
    AND camRun.state = 'full'
    AND chipRun.state = 'full'
    AND chipRun.magicked
    AND camProcessedExp.fault = 0
    AND camProcessedExp.quality = 0
    AND magicDSFile.component IS NULL
UNION
-- warpSkyfiles
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicRun.magic_id,
    magicRun.exp_id,
    magicDSRun.label,
    camera,
    magicMask.uri as streaks_uri,
    NULL AS inv_streaks_uri,
    stage,
    stage_id,
    warpSkyfile.skycell_id as component,
    warpSkyfile.uri,
    warpSkyfile.path_base,
    magicRun.inverse,
    NULL as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM magicDSRun
JOIN magicMask USING (magic_id)
JOIN magicRun USING (magic_id)
JOIN warpRun ON warp_id = stage_id
JOIN warpSkyfile USING(warp_id)
JOIN rawExp ON magicRun.exp_id = rawExp.exp_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    AND magicDSFile.component = warpSkyfile.skycell_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'warp'
    AND warpRun.state = 'full'
    AND warpSkyfile.fault = 0
    AND warpSkyfile.quality = 0
    AND magicDSFile.component IS NULL
UNION
-- regular diffSkyfiles
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicRun.magic_id,
    magicRun.exp_id,
    magicDSRun.label,
    rawExp.camera,
    magicMask.uri as streaks_uri,
    NULL AS inv_streaks_uri,
    stage,
    magicRun.diff_id as stage_id,
    diffSkyfile.skycell_id as component,
    NULL AS uri,
    diffSkyfile.path_base,
    magicRun.inverse,
    NULL as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM rawExp
JOIN magicRun USING (exp_id)
JOIN magicMask USING (magic_id)
JOIN magicDSRun USING(magic_id)
JOIN magicInputSkyfile USING(magic_id)
JOIN diffRun USING(diff_id)
JOIN diffSkyfile
    ON  magicRun.diff_id = diffSkyfile.diff_id
    AND magicInputSkyfile.node = diffSkyfile.skycell_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    AND magicDSFile.component = diffSkyfile.skycell_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'diff'
    AND diffRun.bothways = 0
    AND diffSkyfile.fault = 0
    AND diffSkyfile.quality = 0
    AND magicDSFile.component IS NULL
-- bothways diffSkyfiles
UNION
SELECT DISTINCT
    magicDSRun.magic_ds_id,
    magicRun.magic_id,
    magicRun.exp_id,
    magicDSRun.label,
    rawExp.camera,
    magicMask.uri as streaks_uri,
    (SELECT uri from magicMask where magic_id = inv_magic_id) AS inv_streaks_uri,
    stage,
    magicRun.diff_id as stage_id,
    diffSkyfile.skycell_id as component,
    NULL AS uri,
    diffSkyfile.path_base,
    magicRun.inverse,
    NULL as cam_path_base,
    outroot,
    recoveryroot,
    re_place,
    remove
FROM rawExp
JOIN magicRun USING (exp_id)
JOIN magicMask USING (magic_id)
JOIN magicDSRun USING(magic_id)
JOIN magicInputSkyfile USING(magic_id)
JOIN diffRun USING(diff_id)
JOIN diffSkyfile
    ON  magicRun.diff_id = diffSkyfile.diff_id
    AND magicInputSkyfile.node = diffSkyfile.skycell_id
LEFT JOIN magicDSFile
    ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
    AND magicDSFile.component = diffSkyfile.skycell_id
WHERE
    magicDSRun.state = 'new'
    AND magicDSRun.stage = 'diff'
    AND diffRun.bothways
    AND diffSkyfile.fault = 0
    AND diffSkyfile.quality = 0
    AND magicDSFile.component IS NULL
) as Foo
