Re: difficult JOIN

Hi Jim,

thanks for your answer!

> 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?

Yes you understand me well, thats what i'm trying to achieve.

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

I'm not sure anymore too.

> 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?

This report should not be the final thing. It schould only present the
matched times on an easy way and the tourleader can than edit the
worktimes manually. He have to do this after every tour because the
employees are often not using the timerecording unit correct (i'm
talking about restaurants).

In the meanwhile i gave every record an position counter (per tour and
employee), derrived from begin_time, per trigger, and merged them in
this way:

SELECT * FROM work LEFT JOIN plan USING(id_tour, id_employee, counter)
UNION
SELECT * FROM work RIGHT JOIN plan USING(id_tour, id_employee, counter)

Thats works for me but if the tourleader change one worktime record
the counters have to be recalculate and the order of the records will
change.

This is hard to handle and i think i will do all merging
programmatically in the app or with an ppgsql function.

> 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.

I'm not the father of this strange naming convention :)

Thank you again,
Thomas