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
