From: | Guyren Howe <guyren(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org, stan <stanb(at)panix(dot)com> |
Subject: | Re: Join help, please |
Date: | 2020-03-18 18:57:08 |
Message-ID: | 58c73414-6ad6-46aa-a74d-f96a342cc751@Spark |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <stanb(at)panix(dot)com>, 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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2020-03-18 19:36:24 | Re: Join help, please |
Previous Message | Michael Lewis | 2020-03-18 18:55:25 | Re: Join help, please |