Re: OUTER JOIN and WHERE

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