-- Query to get the status of a "works order".
SELECT
  c.`id`,
  c.`name`,
  h.`HOUSE_KEY`,
  ADDRESS(h.`HOUSE_KEY`) AS `ADDRESS`,
  s.`WORK_CODE` AS `WORK_CODE`,
  IF(w.`WORK_CODE` IS NULL, 'N', 'Y') AS `DONE`,
  COUNT(w.`WORK_CODE`) AS `DONE COUNT`
FROM
  `workcontracts` c
INNER JOIN
  `houses_workcontracts` hw ON (hw.`workcontract_id` = c.`id`)
INNER JOIN
  `gc_bdc`.`house` h ON (h.`HOUSE_KEY` = hw.`HOUSE_KEY`) -- [DB]
INNER JOIN
  `workcontracts_workitems` ww ON (ww.`workcontract_id` = hw.`workcontract_id`)
LEFT OUTER JOIN
  `repairs` r ON (r.`HOUSE_KEY` = hw.`HOUSE_KEY`)
LEFT OUTER JOIN
  `setworkc` s ON (s.`WORK_CODE` = ww.`WORK_CODE`)
LEFT OUTER JOIN
  `workcode` w ON (w.`WORK_CODE` = s.`WORK_CODE`) AND
    (w.`JOB_NO` = r.`JOB_NO`)
WHERE
  c.`id` = 1 -- [CONTRACT]
GROUP BY
  h.`HOUSE_KEY`, s.`WORK_CODE`
ORDER BY
  h.`ROAD`, h.`HOUSENO`