Re: A 3 table join question - Mailing list pgsql-general

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