OUTER JOIN and WHERE

Started by Jeremy Cowgaralmost 24 years ago2 messagesgeneral
Jump to latest
#1Jeremy Cowgar
develop@cowgar.com

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

#2Manfred Koizar
mkoi-pg@aon.at
In reply to: Jeremy Cowgar (#1)
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