On Fri, Aug 16, 2019 at 5:54 PM stan <st...@panix.com> wrote: > > On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote: > > On Fri, Aug 16, 2019 at 7:24 AM rob stone <floripa...@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 > > > > 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) > > > OK, I am clearly not understanding this yet. > > Here is what I am trying: > > > select > COALESCE( > labor_cost_sum_view.proj_no , > material_cost_sum_view.proj_no , > expense_report_cost_sum_view.proj_no > ) > AS > proj_no , > labor_cost_sum_view.labor_cost_sum , > material_cost_sum_view.mtrl_cost , > expense_report_cost_sum_view.burdened_cost , > coalesce( labor_cost_sum_view.labor_cost_sum, 0) > + > coalesce(material_cost_sum_view.mtrl_cost, 0) > + > coalesce(expense_report_cost_sum_view.burdened_cost, 0) > from > labor_cost_sum_view > full join material_cost_sum_view on > material_cost_sum_view.proj_no = labor_cost_sum_view.proj_no > full join expense_report_cost_sum_view on > expense_report_cost_sum_view.proj_no = labor_cost_sum_view.proj_no > ; > > > Having simplified things a bunch, and removing casts and aliases etc. > > But here is what I am getting as a result set: > > proj_no | labor_cost_sum | mtrl_cost | burdened_cost | ?column? > ---------+----------------+-----------+---------------+---------------- > 45 | 3133.17500000 | 5394.6800 | 2564.980000 | 11092.83500000 > 764 | 810.75000000 | 7249.4800 | | 8060.23000000 > 789 | 7015.57500000 | | | 7015.57500000 > 7456 | | 4007.3000 | | 4007.3000 > 33 | | | 241.380000 | 241.380000 > 7456 | | | 1747.110000 | 1747.110000 > (6 row > > Note that project number 7456 appears in 2 rows. > > --
If you look at 7456, it has data in your 2nd and 3rd tables (material & expense/burdened), but not the 1st (labor). So the first two tables (labor & material) get joined for the first row you see there. Then you are joining the 3rd table (expense) to this join on: expense_report_cost_sum_view.proj_no = labor_cost_sum_view.proj_no But there is no labor_cost_sum_view.proj_no for 7456. So the join doesn't match, and you're getting a separate row for the expense column. In the example I gave, the 1st two IDs were coalesced: FULL JOIN t3 ON *(COALESCE(t1.id <http://t1.id/>,t2.id <http://t2.id/>)=t3.id <http://t3.id/>)* Which in your case would translate to: full join expense_report_cost_sum_view on (expense_report_cost_sum_view.proj_no = *COALESCE(labor_cost_sum_view.proj_no,material_cost_sum_view.proj_no))* For 7456, the coalesce will yield the 7456 that would then join to your expense row. Also, FWIW, since your proj_no that you are matching on is the same in all tables, you can join with USING instead. Aside from being a little simpler to write out, you also end up with only one proj_no column instead of 3, and avoids the need to coalesce them as you are doing in the select. 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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.