Re: Table Join Problem

On 4 Nov 2015 02:21, "lmanorders" <lmanorders(at)gmail(dot)com> wrote:
>
> I’m attempting to create a report from the join of two tables:
>
> The table layouts are as follows:
> create table custaccts(acctno char(10) PRIMARY KEY, caname varchar(40), custstat integer, balances numeric(12,2)[5], ...)
> create table distribution(acctno char(10), CONSTRAINT dist_acctno_fkey FOREIGN KEY (acctno)
> REFERENCES custaccts (acctno), distamt numeric(12,2), ...)
>
> The distribution table can have from 0 to many entries for each acctno in the custaccts table. I need to create a selection that returns a list of customer acctno, caname, custstat, sum(balances), and sum(distamt) WHERE the sum of balances is not equal to the sum of distamts for a given acctno.
>
> I have come up with the following, which is close to working, but the result does not include entries in the custaccts table if the sum of balances is greater than zero, but there are no entries for the acctno in the distribution table.
>
> SELECT acctno, caname, custstat, acctbal, disttotl FROM
> (SELECT ca.acctno, caname, custstat, balances[1]+balances[2]+balances[3]+balances[4]-balances[5] AS acctbal, sum(distamt) AS disttotl
> FROM custaccts ca JOIN distribution dr ON ca.acctno = dr.acctno WHERE ca.acctno >= '01-0001.00’ AND ca.acctno <= ‘02-0500.00’
> AND custstat > 4" GROUP BY ca.acctno ORDER BY ca.acctno) AS adsel WHERE acctbal <> disttotl
>
>I also tried changing “JOIN” to “LEFT OUTER JOIN”, but that produced the same result. The result is missing entries from custaccts where the sum of balances is greater than zero but there are no entries for that acctno in distribution.
>
Try a full outer join? With a coalesce on the amount columns to replace nulls with zero?

The coalesce function was exactly what I needed. Thank you!!
Here is the working select:
SELECT acctno, caname, custstat, acctbal, disttotl FROM
(SELECT ca.acctno, caname, custstat, balances[1]+balances[2]+balances[3]+balances[4]-balances[5] AS acctbal,
COALESCE(sum(distamt),0::numeric) AS disttotl
FROM custaccts ca LEFT OUTER JOIN distribution dr ON ca.acctno = dr.acctno
WHERE ca.acctno >= '01-0001.00’ AND ca.acctno <= ‘02-0500.00’ AND custstat > 4 GROUP BY ca.acctno
ORDER BY ca.acctno) AS adsel WHERE acctbal <> disttotl