Re: OUTER JOIN and WHERE

On 18 Jun 2002 23:14:29 -0400, Jeremy Cowgar <develop(at)cowgar(dot)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