-- Get a list of exposures on which magic may be performed
SELECT
    exp_id,
    MAX(diffWarps.diff_id) AS diff_id,
    -- The following trick pulls out the 'inverse' value for the maximum diff_id
    CONVERT(SUBSTRING_INDEX(GROUP_CONCAT(diffWarps.inverse ORDER BY diffWarps.diff_id), ',', 1), UNSIGNED) AS inverse
FROM (
    -- Forward diffs
    SELECT
        diffRun.diff_id,
        warp1 AS warp_id,
        0 AS inverse
    FROM diffRun
    JOIN diffInputSkyfile USING(diff_id)
    WHERE diffInputSkyfile.warp1 IS NOT NULL
        AND diffRun.exposure = 1
    -- WHERE hook %s
    UNION
    -- Backward diffs
    SELECT
        diffRun.diff_id,
        warp2 AS warp_id,
        1 AS inverse
    FROM diffRun
    JOIN diffInputSkyfile USING(diff_id)
    WHERE diffInputSkyfile.warp2 IS NOT NULL
        AND diffRun.exposure = 1
        AND diffRun.bothways = 1
    -- WHERE hook %s
    ) AS diffWarps
JOIN warpRun USING(warp_id)
JOIN fakeRun USING(fake_id)
JOIN camRun USING(cam_id)
JOIN chipRun USING(chip_id)
LEFT JOIN magicRun USING(exp_id)
-- WHERE hook %s
GROUP BY exp_id
