OUTER JOIN and WHERE
От | Jeremy Cowgar |
---|---|
Тема | OUTER JOIN and WHERE |
Дата | |
Msg-id | 1024456392.1480.6.camel@dsktop обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: