database - MySQL Join and COUNT(*) query -

database - MySQL Join and COUNT(*) query -

i have problem. solution downwards lack of sql knowledge on part. have 2 tables.

table : jobs +---+----------+ |id | jobname | +---+----------+ | 1 | tinker | | 2 | tailor | | 3 | soldier | | 4 | sailor | +---+----------+ table : applied +---+--------------+--------+ |id | jobappliedid | name | +---+--------------+--------+ |1 | 1 |cliff | |2 | 2 |john | |3 | 2 |ringo | |4 | 2 |paul | +---+--------------+--------+

now, want create sql simple select on table jobs, ...

select * jobs;

but want bring in select count of how many times each job has been applied for. illustration homecoming following.

+---+----------+-------+ |id | jobname | count | +---+----------+-------+ | 1 | tinker | 1 | | 2 | tailor | 3 | | 3 | soldier | 0 | | 4 | sailor | 0 | +---+----------+-------+

i think need embed select not know start.

can help?

thanks.

you need left bring together so... utilize coalesce handle null values

select j.*, count(a.jobappliedid) numapplied jobs j left bring together applied on a.jobappliedid = j.id grouping j.id

fiddle demo

mysql database