Thread: OUTER JOIN and WHERE

I created an OUTER join between two files (claim and claim_statuses) ...
I want all statuses whether a claim exist in that status or not. My
first SQL worked great, but now I want to limit the results to only one
provider. Here is what I came up with but the problem is that it is only
displaying the statuses that claims are in (1-7) and it may only display
2 and 3.

CREATE VIEW claim_statistics_by_provider AS
    SELECT
        c.provider_id,
        s.id,
        s.name,
        count (c.id) AS total
    FROM
        claims AS c
            RIGHT JOIN
                claim_statuses AS s
            ON c.reduction_status = s.id
    GROUP BY c.provider_id, s.id, s.name;

I then issue:

SELECT * FROM claim_statistics_by_provider WHERE provider_id = 31017;

The results are:

 provider_id | id |       name        | total
-------------+----+-------------------+-------
       31017 |  4 | Done NO Reduction |     1

The results of:

CREATE VIEW claim_statistics AS
    SELECT
        s.id,
        s.name,
        count (c.id) AS total
    FROM
        claims AS c
            RIGHT JOIN
                claim_statuses AS s
            ON c.reduction_status = s.id
    GROUP BY s.id, s.name;

queried by: SELECT * FROM claim_statistics;

are:

 id |       name        | total
----+-------------------+-------
  0 | Untouched         |     56
  1 | Waiting or Reply  |   4056
  2 | Verbal Yes        |  12839
  3 | Done w/Reduction  | 233290
  4 | Done NO Reduction |  13263
  5 | On Hold           |      0
  6 | Ignore            |      0

which is what I want but for provider. What's wrong with my statement
claim_statistics_by_provider ?

Thanks,

Jeremy