-- 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
FROM
    (SELECT
        chipRun.*,
        rawImfile.class_id as rawimfile_class_id,
        chipProcessedImfile.class_id
    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
    ) as Foo
