Re: A 3 table join question
On Fri, Aug 16, 2019 at 7:24 AM rob stone <floriparob@gmail.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
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)
;
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)
;
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)
----+--------+--------+--------
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
В списке pgsql-general по дате отправления: