Get total count of records with a mysql join and 2 tables -

i have 2 tables trying join not sure how make time efficient.

tasks table:

nid | created_by | claimed_by | urgent 1   |     11     |     22     |   1 2   |     22     |     33     |   1 3   |     33     |     11     |   1 1   |     11     |     43     |   0 1   |     11     |     44     |   1 

employee table:

userid | name   11   |  employeea   22   |  employeeb   33   |  employeec 

result trying get:

userid | created_count | claimed_count | urgent_count   11   |       3       |       1       |       3   22   |       1       |       1       |       2   33   |       1       |       1       |       2 

created_account column show total # of tasks created user.

claimed_count column show total # of tasks claimed user.

urgent_count column show total # of urgent tasks (created or claimed) user.

thanks in advance!

i start breaking pieces , putting them together. can created_count , claimed_count using simple aggregation this:

select created_by, count(*) created_count mytable group created_by;  select claimed_by, count(*) claimed_count mytable group claimed_by; 

to urgent count each employee, join 2 tables on condition employee either created_by or claimed_by column, , group employee. instead of counting, however, use sum(). doing because appears each row either 0 or 1, sum() count non-zero rows:

select e.userid, sum(t.urgent) employee e join task t on e.userid in (t.created_by, t.claimed_by) group e.userid; 

now have bits of data need, can use outer join join of subqueries employees table counts. can use coalesce() function replace null counts 0:

select e.userid, coalesce(u.urgent_count, 0) urgent_count, coalesce(crt.created_count, 0) created_count, coalesce(clm.claimed_count, 0) claimed_count employee e left join(   select e.userid, sum(t.urgent) urgent_count   employee e   join task t on e.userid in (t.created_by, t.claimed_by)   group e.userid) u on u.userid = e.userid left join(   select claimed_by, count(*) claimed_count   task   group claimed_by) clm on clm.claimed_by = e.userid left join(   select created_by, count(*) created_count   task   group created_by) crt on crt.created_by = e.userid; 

here sql fiddle example.