Re: A 3 table join question

On Sat, Aug 17, 2019 at 12:24:31AM +1000, rob stone wrote:
> Hello,
> 
> On Fri, 2019-08-16 at 07:39 -0400, stan wrote:
> > First let me say a huge THANK YOU to all the helpful people that
> > hanging out
> > on this.
> > 
> > I am changing from one type of work, going back to some database work
> > for a
> > project, as my old job was eliminated. I have made great progress on
> > this,
> > thanks to the time and effort of lots of folks from this list.
> > 
> > Now, here is my latest stumbling block. I have three "data streams"
> > that all
> > contribute to the total costs of a project:
> > 
> > * labor cost
> > * material cost
> > * expense report cost
> > 
> > I have a view that summarizes the burdened cost from each of these 3
> > streams, and i am trying to create a view that shows the total
> > project cost.
> > 
> > Here is the test data from each of the 3 streams:
> > 
> > stan=> select * from labor_cost_sum_view ;
> >  proj_no | labor_bill_sum | labor_cost_sum 
> >  ---------+----------------+----------------
> >    45 |     10810.0000 |  3133.17500000
> >   764 |      8712.0000 |   810.75000000
> >   789 |     46335.5400 |  7015.57500000
> >  (3 rows)
> > 
> > stan=> select * from material_cost_sum_view ;
> >  proj_no | mtrl_cost 
> >  ---------+-----------
> >    45 | 5394.6800
> >   764 | 7249.4800
> >  7456 | 4007.3000
> > (3 rows)
> > 
> > stan=> select * from expense_report_cost_sum_view ;
> >  proj_no | incured_sum | burdened_cost 
> >  ---------+-------------+---------------
> >    45 |     2564.98 |   2564.980000
> >  7456 |     1747.11 |   1747.110000
> > (2 rows)
> > 
> > And here is the clause for creating the summary table that I
> > presently have:
> > 
> > 
> > DROP VIEW overall_cost_sum_view ;
> > 
> > CREATE view overall_cost_sum_view as 
> > select 
> >     material_cost_sum_view.proj_no as l_proj_vo ,
> >     labor_cost_sum_view.proj_no as m_proj_vo , 
> >     expense_report_cost_sum_view.proj_no as x_proj_vo , 
> >     cast (labor_cost_sum_view.labor_cost_sum as money) as
> > l_burdened_cost,
> >     cast (material_cost_sum_view.mtrl_cost as money)as
> > m_burdened_cost,
> >     cast (expense_report_cost_sum_view.burdened_cost as money)as
> > x_burdened_cost ,
> >     cast (
> >             coalesce( labor_cost_sum_view.labor_cost_sum,
> > 0) 
> >             +
> >                    coalesce(material_cost_sum_view.mtrl_cost, 0)
> >             +
> >                    coalesce(expense_report_cost_sum_view.burdene
> > d_cost, 0)
> >            as money)  as ttl_cost
> > 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
> > ;
> > 
> > Which results in the following:
> > 
> > stan=> select * from overall_cost_sum_view ;
> > -----------+-----------+-----------+-----------------+---------------
> > --+-------- ---------+------------
> >         45 |        45 |        45 |       $3,133.18
> > |       $5,394.68 |       $ 2,564.98 | $11,092.84
> >        764 |       764 |           |         $810.75
> > |       $7,249.48 |                  |  $8,060.23
> >            |       789 |           |       $7,015.58
> > |                 |                  |  $7,015.58
> >       7456
> > |           |           |                 |       $4,007.30
> > |                  |  $4,007.30
> >            |           |      7456
> > |                 |                 |       $ 1,747.11 |  $1,747.11
> > (5 rows)
> > 
> > 
> > As you can see this statement seems to work correctly on the join of
> > the
> > labor and material costs, but fails when I add the expense report
> > stream.
> > 
> > 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.
> 
> How do you know which of the three "streams" contains all proj_no's?
> 
> Maybe you should consider the crosstab code so you end up with
> something like this;-
> 
> proj_no | Labour | Material | Expenses
> 
>   45    | 10810  |   5394   |   2564
>  764    |  8712  |   7249   |      0
>  789    | 46335  |   4007   |   1747
> 
Sorry folks. I am still struggling with this.

Your analysis of my issue is completely correct. 

I read the documentation of the crosstab functionality, but I am having
trouble getting my head wrapped around how it works, and thus how to apply it
to my issues.

As your analysis shows that basic issue is that a given project number can be
in any combination of 1 to all 3 of the feeder tables, and what I am trying
to wind up with in the result of this operation is one row per project
number with values in any column that has non null data in the source
table.

The feeder tables have already been prepared such that there is only 1 row
per project number in each one of them.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin