add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
version()
8.0.30
create table population (
id integer
);

create table recipients (
population_id integer,
employment_condition integer,
has_car integer,
has_land integer
)
insert into population values (1);
insert into population values (2);
insert into population values (3);
insert into recipients values (1,0,1,1);
insert into recipients values (1,1,1,0);
insert into recipients values (1,3,1,1);
select p.id, count(r.population_id) from population p
left join
recipients r on (p.id = r.population_id
and r.employment_condition in (0,1,3)
and r.has_car !=0
and r.has_land !=0
)
group by p.id
id count(r.population_id)
1 2
2 0
3 0
select p.*, ifnull(counter,0) from population p
left join
(select r.population_id, count(*) counter from
recipients r where
r.employment_condition in (0,1,3)
and r.has_car !=0
and r.has_land !=0
group by r.population_id
) c on (c.population_id = p.id)
id ifnull(counter,0)
1 2
2 0
3 0