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

On 18 Jun 2002 23:14:29 -0400, Jeremy Cowgar <develop@cowgar.com>
wrote:
>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,

Jeremy,

so for a row in claim_statuses without a matching row in claims you
get something like

 provider_id | id |       name        | total
-------------+----+-------------------+-------
      (null) |  9 | Xxxx XX Xxxxxxxxx |     0

>but now I want to limit the results to only one
>provider.

If you now apply your WHERE clause (WHERE provider_id = 31017) to this
row, it's clear that this row is not selected.

I guess what you really want is
  1. find all claims that have a provoder_id of 31017
  2. use the result of step 1 in your outer join

Now let's translate this to SQL:
1.
        SELECT * FROM claims WHERE provider_id = 31017;

2.
        SELECT
                s.id,
                s.name,
                count (c.id) AS total
        FROM
                (SELECT * FROM claims WHERE provider_id = 31017) AS c
                        RIGHT JOIN
                                claim_statuses AS s
                        ON c.reduction_status = s.id
        GROUP BY s.id, s.name;

or shorter

        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
                        AND provider_id = 31017
        GROUP BY s.id, s.name;

I'm afraid you cannot use a view, if the provider_id you're looking
for is not always the same.

Servus
 Manfred