Re: difficult JOIN

On Tue, Jan 25, 2005 at 05:26:50PM +0100, Thomas Chille wrote:
> 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.

Hrm. So for a given tour, employee, you want to pair the first record in
plan with the first record in work, and the second record in plan with
the second record in work?

Doing that will be pretty tricky. I'm not sure you can even do it in a
single SELECT.

More important, does it even make sense? What if an employee ends up not
working at all for one of his/her planned times? Every record after that
would be completely skewed. Wouldn't it make much more sense to either
assign an ID to each record in the plan table, and correlate records in
the work table using that ID, or correlate based on begin and end time?

BTW, I've never seen the convention id_employee; people generally use
employee_id. Is it more important to know that you're talking about an
ID or that you're talking about an employee? Just food for thought.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"