Postgres Pro
Downloads
Home   >   mailing lists

Thread: difficult JOIN

difficult JOIN

From
"Thomas Chille (spoon)"
Date:
Hi,

i have the following SQL-Problem:

We are using 2 tables. The first, called plan, is holding planned working times for employees per
tour:

plan.id_tour
plan.id_employee
plan.begin_time
plan.end_time

The second table 'work' stores the actual worked times for employees per tour:

work.id_tour
work.id_employee
work.begin_time
work.end_time

Employees can be multiple times assigned to one tour. One record will be created for every
assignment. They can also work multiple times in one tour.

Now i wanna merge this infos into one report. I wanna join the first plan entry for one employee in
one tour with the first work entry for one employee in one tour and so on.

How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit.

Thanks for any hints,
Thomas

Re: difficult JOIN

From
"Sim Zacks"
Date:
You're going to need a cursor or a for loop. No way to join them.


""Thomas Chille (spoon)"" <t.chille@spoon.de> wrote in message
news:006101c502f9$342920b0$500a0a0a@spoon.de...
> Hi,
>
> i have the following SQL-Problem:
>
> We are using 2 tables. The first, called plan, is holding planned working
times for employees per
> tour:
>
> plan.id_tour
> plan.id_employee
> plan.begin_time
> plan.end_time
>
> The second table 'work' stores the actual worked times for employees per
tour:
>
> work.id_tour
> work.id_employee
> work.begin_time
> work.end_time
>
> Employees can be multiple times assigned to one tour. One record will be
created for every
> assignment. They can also work multiple times in one tour.
>
> Now i wanna merge this infos into one report. I wanna join the first plan
entry for one employee in
> one tour with the first work entry for one employee in one tour and so on.
>
> How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not
doit.
>
> Thanks for any hints,
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Re: difficult JOIN

From
"Sim Zacks"
Date:
You're going to need a cursor or a for loop. No way to join them.


""Thomas Chille (spoon)"" <t.chille@spoon.de> wrote in message
news:006101c502f9$342920b0$500a0a0a@spoon.de...
> Hi,
>
> i have the following SQL-Problem:
>
> We are using 2 tables. The first, called plan, is holding planned working
times for employees per
> tour:
>
> plan.id_tour
> plan.id_employee
> plan.begin_time
> plan.end_time
>
> The second table 'work' stores the actual worked times for employees per
tour:
>
> work.id_tour
> work.id_employee
> work.begin_time
> work.end_time
>
> Employees can be multiple times assigned to one tour. One record will be
created for every
> assignment. They can also work multiple times in one tour.
>
> Now i wanna merge this infos into one report. I wanna join the first plan
entry for one employee in
> one tour with the first work entry for one employee in one tour and so on.
>
> How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not
doit.
>
> Thanks for any hints,
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>