Join help, please

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Join help, please

stan-9
I am confused. given this view:


AS
SELECT
        employee.id ,
        work_type.type ,
        permit ,
    work_type.overhead ,
    work_type.descrip  
from
        permitted_work
inner join employee on
        employee.employee_key = permitted_work.employee_key
inner join work_type on
        work_type.work_type_key = work_type.work_type_key
        ;

Why do I have 38475 rows, when the base table only has 855?

My thinking was that the inner joins would constrain this view to the rows
that exist in the base (permitted_work) table.

Clearly I am misunderstanding something basic here.


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


Reply | Threaded
Open this post in threaded view
|

Re: Join help, please

Michael Lewis
work_type.work_type_key = work_type.work_type_key

You've done a cross join.
Reply | Threaded
Open this post in threaded view
|

Re: Join help, please

Guyren Howe
In reply to this post by stan-9
The three types of thing (permitted_work; employee; work_type) don’t stand in a 1:1 relationship with each other. You might have multiple work_types or permitted_work for each employee, I’m guessing.

Each existing combination produces one row in the result. So an employee with three permitted_works and 4 work types will produce 12 rows in the joined result.

If you want one row per employee, you might consider using array_agg with group_by to collapse the multiple work_types or permitted_works into arrays alongside the employee information.
On Mar 18, 2020, 11:51 -0700, stan <[hidden email]>, wrote:
I am confused. given this view:


AS
SELECT
employee.id ,
work_type.type ,
permit ,
work_type.overhead ,
work_type.descrip
from
permitted_work
inner join employee on
employee.employee_key = permitted_work.employee_key
inner join work_type on
work_type.work_type_key = work_type.work_type_key
;

Why do I have 38475 rows, when the base table only has 855?

My thinking was that the inner joins would constrain this view to the rows
that exist in the base (permitted_work) table.

Clearly I am misunderstanding something basic here.


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


Reply | Threaded
Open this post in threaded view
|

Re: Join help, please

Michael Lewis
In reply to this post by Michael Lewis


On Wed, Mar 18, 2020, 12:55 PM Michael Lewis <[hidden email]> wrote:
work_type.work_type_key = work_type.work_type_key

You've done a cross join.


You meant to do permitted_work.work_type_key = work_type.work_type_key I expect