Re: A 3 table join question

On Fri, Aug 16, 2019 at 4:39 AM stan <stanb(at)panix(dot)com> 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.burdened_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?
>

Not sure (not willing to dig deep enough to solve) though it is likely
related missing projects on a given table that is then being used for a
join.

What I would generally do here is:

SELECT
(
SELECT proj_id FROM tbl1
UNION
SELECT proj_id FROM tbl2
UNION
SELECT proj_id FROM tbl3
) AS available_projects
LEFT JOIN tbl1 USING proj_id
LEFT JOIN tbl2 USING proj_id
LEFT JOIN tbl3 USING proj_id

Converting multiple full joins into a series of left joins by adding a
complete right hand table makes reasoning and constructing the query less
prone to errors. Ideally you could avoid the UNIONs by maintaining a table
of projects to join against.

In any case to directly solve this in the full join form you probably want
to do something like:

SELECT ...
FROM tbl1
FULL JOIN (tbl2 AS tbl2alias (proj2_id) FULL JOIN tbl3 AS tbl3alias
(proj3_id) ON proj2_id = proj3_id) AS tbl23
ON (tbl23.proj2_id = tbl1.proj_id OR tbl23.proj3_id = tbl1.proj_id)

Honestly there is probably a way to do it without the aliased full
join...though you are going to need an "OR" someplace - your initial FROM
table doesn't contain all projects.

David J.

P.S. experience and the common perception suggest avoiding the money type
and using numeric instead. I find the money type should be limited to data
injestion only.