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

From David G. Johnston
Subject Re: A 3 table join question
Date
Msg-id CAKFQuwat-usExcTh29eUccpae6zsXsYi24NG=RpakYOcGUbjDA@mail.gmail.com
Whole thread Raw
In response to A 3 table join question  (stan <stanb@panix.com>)
List pgsql-general
On Fri, Aug 16, 2019 at 4:39 AM stan <stanb@panix.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.  

pgsql-general by date:

Previous
From: Peter Grman
Date:
Subject: Re: Bad Estimate for complex query with JOINS on subselects and OR inwhere conditions
Next
From: rob stone
Date:
Subject: Re: A 3 table join question