SQL Problem

I have a problem which I have have fictionalized below. Consider the career history of fictional employees as shown in the table career_history (extract below). this details the store at which each employee works and a starting and ending period when they were employed there. so employee 1 starts at store 1 in period 01, works there until period 8 and then transfers to store 2 where he works for the rest of the year.

If I wish to update the table career_snapshot_11 with his store at period 11 I would issue

update career_snapshot_11 s
set storeid = (select storeid from career_history h where h.empno = s.empo
and s.period between h.start_period and h.end_period and s.period = 11);

leading to the first row shown in career_snapshot.

unfortunately for employee2 the statement would fail with

single-row subquery returns more than one row

because employee 2 is apparently working at both stores in this time period.

So my problem is

Given only career_history how can I find all employees who are shown as working at different stores in the same period. We the of course have in reality the problem of finding out which of the two rows is correct and which is wrong, but that is a different story.

career_history
storeid empno start_period end_period

1            1            01                    08
2            1            09                    12
2            2            01                    11
1            2            07                    12

career_snapshot_11
empno, storeid, period

1           2          11
2            <?>     11

I have considered a number of different approaches to this, but really can't come up with a neat solution (which I am sure must exist).

Niall Litchfield
Oracle DBA
Audit Commission UK Received on Thu Sep 27 2001 - 15:46:09 CDT