CREATE TABLE appeal_applicable_areas (applicable_area_id int, suitno int);

CREATE TABLE supreme_applicable_areas(applicable_area_id int, suitno int);

CREATE TABLE cp_cases_counsel (suitno int, councel_id int);

INSERT INTO applicable_areas (area) VALUES

( 1001)

, ( 1002)

, ( 1003)

, ( 1004)

, ( 1005)

, ( 1006)

, ( 1007)

;

SELECT * FROM applicable_areas;

INSERT INTO appeal_applicable_areas (applicable_area_id, suitno) VALUES

( 1, 2001)

, ( 2, 2002)

, ( 3, 2003)

, ( 4, 2004)

, ( 4, 2009)

, ( 4, 2010)

;

INSERT INTO supreme_applicable_areas (applicable_area_id, suitno) VALUES

( 1, 3001)

, ( 2, 3002)

, ( 5, 3003)

, ( 6, 3004)

, ( 7, 3005)

, ( 7, 3006)

, ( 7, 3007)

, ( 7, 3008)

;

SELECT 1, null, applicable_area_id, suitno FROM appeal_applicable_areas

UNION

SELECT null, 1, applicable_area_id, suitno FROM supreme_applicable_areas

)

SELECT applicable_areas.area

, applicable_areas.id as id

, count(stype) as supreme_court_cases

, count(atype) as appeal_court_cases

FROM applicable_areas

JOIN area_types

ON area_types.applicable_area_id = applicable_areas.id

JOIN cp_cases_counsel

ON cp_cases_counsel.suitno = area_types.suitno

WHERE cp_cases_counsel.councel_id = 54

GROUP BY applicable_areas.id

ORDER BY applicable_areas.area ASC

;

, applicable_areas.id as id

, count(stype) as supreme_court_cases

, count(atype) as appeal_court_cases

FROM applicable_areas

JOIN (

SELECT 1 AS atype, null AS stype, applicable_area_id, suitno FROM appeal_applicable_areas

UNION

SELECT null , 1 , applicable_area_id, suitno FROM supreme_applicable_areas

) AS area_types

ON area_types.applicable_area_id = applicable_areas.id

JOIN cp_cases_counsel

ON cp_cases_counsel.suitno = area_types.suitno

WHERE cp_cases_counsel.councel_id = 54

GROUP BY applicable_areas.id

ORDER BY applicable_areas.area ASC

;