Force a value of 0 for non-existing value

Question

Group, I am going to try and explain this as best I can, and I hope it makes (some) sense. I am pulling data from a view I have created that tells me a "Sponsor's" customer types and how many of those accounts by CustomerType are inactive after 1 year, 2 years and 3 years. For example:

SponsorID | CustomerType | ExpiredAfter | Total
1234        D              1 Year         5
1234        RA             1 Year         7
1234        CA             1 Year         3
1234        D              2 Years        2
1234        RA             2 Years        4
1234        CA             2 Years        1
1234        D              3 Years        8
1234        RA             3 Years        1
1234        CA             3 Years        6

The problem I am having is a SponsorID might not have any RA that ExpiredAfter 1 year, so nothing shows up in the data. So instead of getting:

SponsorID | CustomerType | ExpiredAfter | Total
1235        D              1 Year         5
1235        RA             1 Year         0
1235        CA             1 Year         3

I get:

SponsorID | CustomerType | ExpiredAfter | Total
1235        D              1 Year         5
1235        CA             1 Year         3

Again, not sure if any of this makes any sense, but what I want to do is include all customer types for all ExpiredAfter regardless if there are values or not. If there isn't a value I just want to force a 0 or Null. I need that data row with 0 for reporting purposes.

Any suggestions?

Answer 1

It sounds like you need to use a LEFT JOIN instead of an INNER JOIN.

Answer 2

You may also have to use ISNULL(total, 0) or COALESCE(total, 0) depending on which DBMS you are using, and your query (as stated by womp)... because if TOTAL is calculated, then having a null value from a LEFT JOIN would still leave you without a 0 value.