existingSocialHousing.sql

SELECT p.permission_id AS permission_id,
       COUNT(prl.permission_id) AS branches,
       SUM(erl.number_of_units) AS existingSocialHousing,
       status_rc,
       borough_ref,
       b.name AS borough_name,
       permission_date,
       permission_lapses_date,
       decision_agency_rc,
       p.descr,
       sec_add_obj_name,
       prim_add_obj_name,
       street,
       town,
       post_code,
       started_date,
       lapsed_yn,
       p.completed_date,
       p.superseded_date
FROM 
  (
    (
       (
       app_ldd.ld_permissions AS p LEFT JOIN app_ldd.ld_boroughs AS b
         ON p.borough_id
            =b.borough_id
       )
       LEFT JOIN app_ldd.ld_ref_codes AS rc
         ON p.permission_type_rc
            =rc.code
     ) LEFT JOIN app_ldd.ld_prop_res_lines AS prl
         ON p.permission_id
            =prl.superseded_permission_id
  ) LEFT JOIN app_ldd.ld_exist_res_lines AS erl
      ON p.permission_id
        =erl.permission_id

WHERE status_rc != 'DELETED' AND erl.tenure_type_rc = 'S'
GROUP BY p.permission_id,
       status_rc,
       borough_ref,
       b.name,
       permission_date,
       permission_lapses_date,
       decision_agency_rc,
       p.descr,
       sec_add_obj_name,
       prim_add_obj_name,
       street,
       town,
       post_code,
       started_date,
       p.completed_date,
       p.superseded_date
HAVING COUNT(prl.permission_id) = 0
        AND SUM(erl.number_of_units) > 0
;