OUTER JOIN and WHERE

From: Jeremy Cowgar <develop(at)cowgar(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: OUTER JOIN and WHERE
Date: 2002-06-19 03:14:29
Message-ID: 1024456474.1480.8.camel@dsktop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tien Tran Ngoc 2002-06-19 03:17:32 ISAM access in PostgreSQL?
Previous Message Jeremy Cowgar 2002-06-19 03:13:11 OUTER JOIN and WHERE