SELECT DISTINCT
    magic_ds_id
FROM
    (
-- raw stage
SELECT 
    magicDSRun.magic_ds_id
    FROM magicDSRun
    JOIN rawImfile ON stage_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 = 'run'
        AND magicDSRun.stage = 'raw'
    GROUP BY
        magicDSRun.magic_ds_id,
        rawImfile.exp_id
    HAVING
        COUNT(rawImfile.class_id) = COUNT(magicDSFile.component)
        AND SUM(magicDSFile.fault) = 0
UNION
-- chip stage
SELECT 
    magicDSRun.magic_ds_id
    FROM magicDSRun
    JOIN chipProcessedImfile ON stage_id = chip_id
    LEFT JOIN magicDSFile
        ON magicDSFile.magic_ds_id = magicDSRun.magic_ds_id
        AND magicDSFile.component = chipProcessedImfile.class_id
    WHERE
        magicDSRun.state = 'run'
        AND magicDSRun.stage = 'chip'
    GROUP BY
        magic_ds_id,
        chip_id
    HAVING
        COUNT(chipProcessedImfile.class_id) = COUNT(magicDSFile.component)
        AND SUM(magicDSFile.fault) = 0
UNION
-- warp stage
SELECT 
    magicDSRun.magic_ds_id
    FROM magicDSRun
    JOIN warpSkyfile on stage_id = warp_id
    LEFT JOIN magicDSFile
        ON magicDSRun.magic_ds_id = magicDSFile.magic_ds_id
        AND magicDSFile.component = warpSkyfile.skycell_id
    WHERE
        magicDSRun.state = 'run'
        AND magicDSRun.stage = 'warp'
        AND warpSkyfile.fault = 0
        AND warpSkyfile.ignored = 0
    GROUP BY
        magicDSRun.magic_ds_id,
        warp_id
    HAVING
        COUNT(warpSkyfile.skycell_id) = COUNT(magicDSFile.component)
        AND SUM(magicDSFile.fault) = 0
UNION
-- diff stage
SELECT DISTINCT
    magicDSRun.magic_ds_id
    FROM magicDSRun
    JOIN magicRun USING (magic_id)
    JOIN magicInputSkyfile USING(magic_id)
    JOIN diffSkyfile
        ON magicInputSkyfile.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 = 'run'
        AND magicDSRun.stage = 'diff'
        AND diffSkyfile.fault = 0
    GROUP BY
        magicDSRun.magic_ds_id,
        magicRun.magic_id
    HAVING
        COUNT(magicInputSkyfile.node) = COUNT(magicDSFile.component)
        AND SUM(magicDSFile.fault) = 0

   ) as Foo
