Обсуждение: A 3 table join question

Поиск
Список
Период
Сортировка

A 3 table join question

От
stan
Дата:
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?

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

Re: A 3 table join question

От
"David G. Johnston"
Дата:
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.  

Re: A 3 table join question

От
rob stone
Дата:
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

Re: A 3 table join question

От
stan
Дата:
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

Re: A 3 table join question

От
Ken Tanzer
Дата:


On Fri, Aug 16, 2019 at 7:24 AM rob stone <floriparob@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)


Cheers,
Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: A 3 table join question

От
stan
Дата:
On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote:
> On Fri, Aug 16, 2019 at 7:24 AM rob stone <floriparob@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.

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

Re: A 3 table join question

От
Ken Tanzer
Дата:


On Fri, Aug 16, 2019 at 5:54 PM stan <stanb@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 <floriparob@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,t2.id)=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
(253) 245-3801

learn more about AGENCY or
follow the discussion.