By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Outer Join help please

P: n/a
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
<ro**@campbell-lange.net>
<www.campbell-lange.net>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Fri, 19 Sep 2003 10:10:17 +0100, Rory Campbell-Lange
<ro**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.