mysql - Joining tables but needs 0 for empty rows -

i don't know how explain scenario using words. writing examples:

i have table named tbltype:

type_id  |  type_name --------------------- 1        |  abb 2        |  cda 3        |  edg 4        |  hij 5        |  klm 

and have table named tblrequest:

req_id  |  type_id  |  user_id  |  duration ------------------------------------------- 1       |  4        |  1002     |  20 2       |  1        |  1002     |  60   3       |  5        |  1008     |  60 .... 

so trying is, fetch sum() of duration each type, particular user.

this tried:

    select          sum(r.`duration`) `duration`,         t.`type_id`,         t.`type_name`     `tblrequest` r         left join `tbltype` t on r.`type_id` = t.`type_id`     r.`user_id` = '1002'      group r.`type_id`  

it might return this:

type_id | type_name | duration ------------------------------- 1       |  abb      | 60 4       |  hij      | 20 

it works. issue is, want 0 value other types doesn't have row in tblrequest. mean want output this:

type_id | type_name | duration ------------------------------- 1       |  abb      | 60 2       |  cda      | 0 3       |  edg      | 0 4       |  hij      | 20 5       |  klm      | 0 

i mean should rows of types, 0 value type doesn't have row in tblrequest

you perform aggregation on tblrequest , join it, using left join handle missing rows , coalesce convert nulls 0s:

select    t.type_id, type_name, coalesce(sum_duration, 0) duration      tbltype t left join (select   type_id, sum(duration) sum_duration                tblrequest               user_id = '1002'            group type_id) r on t.type_id = r.type_id