Joining tables but needs 0 for empty rows

I don't know how to explain the scenario using words. So am writing the examples:

I have a table named tblType:

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

And I have another table named tblRequest:

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

So what am trying to do is, fetch the SUM() of duration for each type, for a particular user.

This is what I tried:

    SELECT 
        SUM(r.`duration`) AS `duration`,
        t.`type_id`,
        t.`type_name`
    FROM `tblRequest` AS r
        LEFT JOIN `tblType` AS t ON r.`type_id` = t.`type_id`
    WHERE r.`user_id` = '1002' 
    GROUP BY r.`type_id` 

It might return something like this:

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

It works. But the issue is, I want to get 0 as value for other types that doesn't have a row in tblRequest. I mean I want the output to be like this:

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

I mean it should get the rows of all types, but 0 as value for those type that doesn't have a row in tblRequest

Answers


You could perform the aggregation on tblRequest and only then join it, using a left join to handle missing rows and coalesce to convert the nulls to 0s:

SELECT    t.type_id, type_name, COALESCE(sum_duration, 0) AS duration
FROM      tblType t
LEFT JOIN (SELECT   type_id, SUM(duration) AS sum_duration
           FROM     tblRequest
           WHERE    user_id = '1002'
           GROUP BY type_id) r ON t.type_id = r.type_id

Select a.type_id, isnull(sum(b.duration), 0)
From tblType a Left Outer Join tblRequest b 
ON a.type_id = b.type_id and b.user_id = 1002
Group by a.type_id

Need Your Help

Eclipse: how to run plugin test as part of local build

eclipse testing ant eclipse-pde

Due to incomplete understanding of eclipse infrastructure I have one problem.

Installing RVM seems to have stopped my cucumber tests running on sauce labs

ruby cucumber rvm capybara saucelabs

After I installed RVM (A requirement for another project I am working on at the same time) I can no longer run my cucumber tests against SauceLabs. I have got RVM using system ruby (1.8.7 p374) and...