Thread: Outer Join help please

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>

On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
>The sent
>column shows the number of items sent to each recipient from each
>source. The received column [...] is summing the number of messages
> by recipient [and] need to be filtered by source too.

SELECT t_to AS recipient,
       t_from AS sender,
       count(*) AS sent,
       sum(CASE WHEN dlr = 1 THEN 1 ELSE 0 END) AS received,
       sum(CASE WHEN dlr = 1 THEN 0 ELSE 1 END) AS outstanding
  FROM dlr
 GROUP BY t_to, t_from;

gives

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

but I'm not sure whether this is what you want.  I didn't even use a
join ...

If it meets your requirements and you are sure that dlr is always 0,
1, or NULL, then here is a simpler version:

SELECT t_to AS recipient,
       t_from AS sender,
       count(*) AS sent,
       sum(dlr) AS received,
       count(*) - sum(dlr) AS outstanding
  FROM dlr
 GROUP BY t_to, t_from;

Servus
 Manfred