drop table if exists size;create table size ( id int primary key, name char(1));insert into size set id=1, name='S';insert into size set id=2, name='M';insert into size set id=3, name='L';drop table if exists person;create table person ( id int primary key, sid int, foreign key (sid) references size(id));-- note: no one is wearing Linsert into person set id=1, sid=1;insert into person set id=2, sid=1;insert into person set id=3, sid=2;insert into person set id=4, sid=2;insert into person set id=5, sid=2;-- does what i want, except leaves out sizes with totals of 0select s.name as size, count(s.id) as totalfrom size s inner join person p on s.id=p.sidgroup by s.idorder by s.id;-- does exactly what i want, but i get the feeling there's a better wayselect s.name as size, sum(case when p.sid is null then 0 else 1 end) as totalfrom size s left join (select sid from person) p on s.id=p.sidgroup by s.idorder by s.id;