Outer Join help please - Mailing list pgsql-general

I'm having troube doing a left outer self join on a table. The sent
column shows the number of items sent to each recipient from each
source. The received column (generated by the outer join) is incorrect
because although it is summing the number of messages by recipient,
these need to be filtered by source too.

How can I do a join on two columns in the master table?

Thanks for any help.
Rory


 recipient | source | sent | received | outstanding
-----------+--------+------+----------+-------------
 22        | 1      |    3 |        2 |           1
 23        | 1      |    1 |        1 |           0
 25        | 1      |    1 |        2 |          -1
 25        | 2      |    1 |        2 |          -1
 26        | 2      |    2 |        0 |
 27        | 2      |    3 |        0 |
(6 rows)



----------------------- function definition -----------------------


DROP TYPE dlr_report CASCADE;

CREATE TYPE dlr_report as (
    recipient    VARCHAR,
    source       VARCHAR,
    sent         INTEGER,
    received     INTEGER,
    outstanding  INTEGER
);


CREATE OR REPLACE FUNCTION report_on_dlr ()
    RETURNS SETOF dlr_report
    AS '
DECLARE
    resulter               dlr_report%rowtype;
BEGIN

FOR resulter IN
    SELECT
        dd.t_to                                as recipient,
        dd.t_from                              as source,
        count(dd.id)                           as sent,
        CASE
            WHEN received_ok is NULL THEN 0
            ELSE received_ok
            END                                as received,
        count(dd.id) - received_ok             as outstanding
    FROM
        dlr dd
        LEFT OUTER JOIN (
            SELECT
                t_to as target, count(id) as received_ok
            FROM
                dlr
            WHERE
                dlr = 1    and t_from = source
            GROUP BY
                target
        ) AS ok
        ON t_to  = ok.target
    GROUP BY
        dd.t_to, dd.t_from, received, received_ok
    ORDER BY
        dd.t_to, dd.t_from
     LOOP

    RETURN NEXT
        resulter;

END LOOP;

RETURN;

END;'
    LANGUAGE plpgsql;

------------------------- data definition -------------------------

CREATE TABLE dlr (
    id serial NOT NULL,
    t_to character varying(30),
    t_from character varying(30),
    dlr smallint
);

COPY dlr (id, t_to, t_from, dlr) FROM stdin;
1    22    1    \N
2    22    1    1
3    22    1    1
4    23    1    1
5    25    1    1
6    25    2    1
7    26    2    \N
8    26    2    0
9    27    2    0
10    27    2    0
11    27    2    0
\.

SELECT pg_catalog.setval ('dlr_id_seq', 11, true);



--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>