SELECT 
  corr_id,
  dvodb,
  filter,
  state,
  workdir,
  label,
  reduction,
  region,
  chip_count,
  cam_count
FROM 
  (SELECT 
     flatcorrRun.*,
     flatcorrChipLink.corr_id as chip_corr_id,
     count(flatcorrChipLink.chip_id) as chip_count
   FROM flatcorrRun
   JOIN flatcorrChipLink
     ON flatcorrChipLink.corr_id = flatcorrRun.corr_id
   GROUP BY
     flatcorrChipLink.corr_id) AS t1
LEFT JOIN
  (SELECT 
     flatcorrCamLink.corr_id as cam_corr_id,
     count(flatcorrCamLink.cam_id) as cam_count
   FROM flatcorrCamLink
   JOIN camRun
       ON flatcorrCamLink.cam_id = camRun.cam_id
   WHERE camRun.state = 'full'
   GROUP BY
       flatcorrCamLink.corr_id) AS t2
ON t1.chip_corr_id = t2.cam_corr_id
WHERE chip_count = cam_count
AND state = 'new'
