-- the output of this query must match the format of chipRun row
SELECT DISTINCT
    chip_id,
    exp_id,
    state,
    workdir,
    workdir_state,
    label,
    reduction,
    expgroup,
    dvodb,
    tess_id,
    end_stage,
    all_files_magicked as magicked
FROM
    (SELECT
        chipRun.*,
        rawImfile.class_id as rawimfile_class_id,
        chipProcessedImfile.class_id,
        SUM(!chipProcessedImfile.magicked) = 0 as all_files_magicked
    FROM chipRun
    JOIN rawImfile
        ON chipRun.exp_id = rawImfile.exp_id
        AND rawImfile.ignored = 0
    LEFT JOIN chipProcessedImfile
        ON chipRun.chip_id = chipProcessedImfile.chip_id
        AND rawImfile.exp_id = chipProcessedImfile.exp_id
        AND rawImfile.class_id = chipProcessedImfile.class_id
    WHERE
        chipRun.state = 'new'
    GROUP BY
        chipRun.chip_id,
        chipRun.exp_id
    HAVING
        COUNT(rawImfile.class_id) = COUNT(chipProcessedImfile.class_id)
        AND SUM(chipProcessedImfile.fault) = 0
        AND SUM(chipProcessedImfile.quality > 0) != COUNT(*)
    ) as Foo
