YYZ2B8Fw

1create schema zoneTest;
2use zoneTest;
3
4-- drop table if exists `#__zones`;
5create table `#__zones`
6(	id int not null,
7	name varchar(100) not null,
8	description varchar(100) not null,
9    status int not null
10);
11-- drop table if exists `#__zones_data`;
12create table `#__zones_data`
13(	zone_id int not null,
14	country_id int not null
15);
16insert `#__zones` (id,name,description,status) values
17(1,'a','descr a',9),
18(2,'b','descr b',9),
19(3,'c','descr c',9);
20insert `#__zones_data` (zone_id,country_id) values
21(1,-1),
22(2,1),(2,2),(2,3),(2,4);
23-- note 3 is missing data (left join)
24
25-- select * from `#__zones`;
26-- select * from `#__zones_data`;
27
28SELECT 
29	Z.id,
30	Z.name,
31	Z.description,
32	Z.status,
33	-- IF(D.country_id = "-1", "All countries", COUNT(DISTINCT(D.country_id))) AS countries
34	min(D.country_id),
35    COUNT(DISTINCT(D.country_id)) theCount
36FROM 
37    `#__zones` AS Z
38LEFT JOIN
39    `#__zones_data` AS D
40ON
41    (Z.id = D.zone_id)      
42GROUP BY Z.id, Z.name, Z.description, Z.status;
43
44drop schema zoneTest; -- cleanup
45-- output: http://i.imgur.com/XM0Ux5t.jpg
46-- stackoverflow question: http://stackoverflow.com/q/40390022