Re: A 3 table join question

On Fri, Aug 16, 2019 at 7:24 AM rob stone <floriparob(at)gmail(dot)com> wrote:

> Hello,
>
> On Fri, 2019-08-16 at 07:39 -0400, stan wrote:
> > What am I doing wrong here?
> >
>
>
> Your view assumes that all three "streams" contain all the proj_no's
> whereas your test data for expense_report_cost_sum_view has no proj_no
> = 764.
>
>
Hi. I'm probably missing something, but it seems simpler to either join
with USING, or by COALESCEing the two ID fields in left part of the JOIN
clause (COALESCE(t1.proj_no,t2.proj_no)=t3.proj_no).

Cheers,
Ken

CREATE TEMP TABLE t1 (id int, t1_val TEXT);
INSERT INTO t1 VALUES (2,'T1_2');
INSERT INTO t1 VALUES (5,'T1_5');
INSERT INTO t1 VALUES (7,'T1_7');
INSERT INTO t1 VALUES (10,'T1_10');

CREATE TEMP TABLE t2 (id int, t2_val TEXT);
INSERT INTO t2 VALUES (3,'T2_3');
INSERT INTO t2 VALUES (5,'T2_5');
INSERT INTO t2 VALUES (6,'T2_6');
INSERT INTO t2 VALUES (10,'T2_10');

CREATE TEMP TABLE t3 (id int, t3_val TEXT);
INSERT INTO t3 VALUES (4,'T3_4');
INSERT INTO t3 VALUES (6,'T3_6');
INSERT INTO t3 VALUES (7,'T3_7');
INSERT INTO t3 VALUES (10,'T3_10');

SELECT id,t1_val,t2_val,t3_val
FROM
t1
FULL JOIN t2 USING (id)
FULL JOIN t3 USING (id)
;

SELECT COALESCE(t1.id,t2.id,t3.id) AS id,t1_val,t2_val,t3_val
FROM
t1
FULL JOIN t2 ON (t1.id=t2.id)
FULL JOIN t3 ON (COALESCE(t1.id,t2.id)=t3.id)
;

id | t1_val | t2_val | t3_val
----+--------+--------+--------
2 | T1_2 | |
3 | | T2_3 |
4 | | | T3_4
5 | T1_5 | T2_5 |
6 | | T2_6 | T3_6
7 | T1_7 | | T3_7
10 | T1_10 | T2_10 | T3_10
(7 rows)

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.