SELECT DISTINCT
    dist_id,
    stage,
    stage_id,
    outroot
FROM
    (
-- raw stage
SELECT 
    distRun.dist_id,
    stage,
    stage_id,
    outroot
    FROM distRun
    JOIN rawImfile ON stage_id = rawImfile.exp_id
    LEFT JOIN distComponent
        ON distRun.dist_id = distComponent.dist_id
        AND distComponent.component = rawImfile.class_id
    WHERE
        distRun.state = 'new'
        AND distRun.fault = 0
        AND distRun.stage = 'raw'
    GROUP BY
        distRun.dist_id,
        rawImfile.exp_id
    HAVING
        COUNT(rawImfile.class_id) = COUNT(distComponent.component)
        AND SUM(distComponent.fault) = 0
UNION
-- chip stage
SELECT 
    distRun.dist_id,
    stage,
    stage_id,
    outroot
    FROM distRun
    JOIN chipProcessedImfile ON stage_id = chipProcessedImfile.chip_id
    LEFT JOIN distComponent
        ON distComponent.dist_id = distRun.dist_id
        AND distComponent.component = chipProcessedImfile.class_id
    WHERE
        distRun.state = 'new'
        AND distRun.fault = 0
        AND distRun.stage = 'chip'
    GROUP BY
        dist_id,
        chipProcessedImfile.chip_id
    HAVING
        COUNT(chipProcessedImfile.class_id) = COUNT(distComponent.component)
        AND SUM(distComponent.fault) = 0
UNION
-- warp stage
SELECT 
    distRun.dist_id,
    stage,
    stage_id,
    outroot
    FROM distRun
    JOIN warpSkyfile on stage_id = warp_id
    LEFT JOIN distComponent
        ON distRun.dist_id = distComponent.dist_id
        AND distComponent.component = warpSkyfile.skycell_id
    WHERE
        distRun.state = 'new'
        AND distRun.fault = 0
        AND distRun.stage = 'warp'
--        AND warpSkyfile.fault = 0
--        AND warpSkyfile.ignored = 0
    GROUP BY
        distRun.dist_id,
        warp_id
    HAVING
        COUNT(warpSkyfile.skycell_id) = COUNT(distComponent.component)
        AND SUM(distComponent.fault) = 0
UNION
-- diff stage
SELECT DISTINCT
    distRun.dist_id,
    stage,
    stage_id,
    outroot
    FROM distRun
    JOIN diffSkyfile
        ON distRun.dist_id = diffSkyfile.diff_id
    LEFT JOIN distComponent
        ON distRun.dist_id = distComponent.dist_id
        AND distComponent.component = diffSkyfile.skycell_id
    WHERE
        distRun.state = 'new'
        AND distRun.fault = 0
        AND distRun.stage = 'diff'
--        AND diffSkyfile.fault = 0
    GROUP BY
        distRun.dist_id,
        diff_id
    HAVING
        COUNT(diffSkyfile.skycell_id) = COUNT(distComponent.component)
        AND SUM(distComponent.fault) = 0
UNION
-- stack stage
SELECT 
    distRun.dist_id,
    stage,
    stage_id,
    outroot
    FROM distRun
    JOIN stackSumSkyfile on stage_id = stack_id
    LEFT JOIN distComponent
        ON distRun.dist_id = distComponent.dist_id
    WHERE
        distRun.state = 'new'
        AND distRun.fault = 0
        AND distRun.stage = 'stack'
        AND distComponent.component IS NOT NULL
        AND distComponent.fault = 0
) as Foo
