Correlated Subqueries Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Correlated Subqueries Question

Author

Topic

dpiper
Starting Member

4 Posts

Posted - 2004-01-18 : 20:33:06

I have a table which lists the job positions a person holds. They can only hold one position at a time, however they may hold a number of positions over the duration of their employment. The key fields are:
1. PositionID (Key); and
2. EmployeeID
3. PositionEndDate

I need to find out for each employee what is their current position or if they have left the company, what was their last position.

I don't know how to search through the Position table to find the latest PositionEndDate for each employee where the employee has left, i.e. their PositionEndDate is not null.

I thought correlated subqueries might do the trick, but my code below seems to return all the values in the table and not the last position:

SELECT EmployeeID, PositionID
FROM EMPOS p1
WHERE PositionEndDate IS NULL OR
EXISTS (SELECT MAX(p2.PositionEndDate)
FROM EMPOS p2
WHERE p1.EmployeeID = p2.EmployeeID)

Any help would be appreciated.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-18 : 20:41:54

Hey, another Sydneysider! Welcome

Rather than exists, you want to check that the PositionEndDate equals the maximum position end date.

Like this :

SELECT EmployeeID, PositionID
FROM EMPOS p1
WHERE PositionEndDate IS NULL OR
PositionEndDate = (SELECT MAX(p2.PositionEndDate)
FROM EMPOS p2
WHERE p1.EmployeeID = p2.EmployeeID)

Exists returns a true or false, so you will return anyone that had a position.

Damian

dpiper
Starting Member

4 Posts

Posted - 2004-01-19 : 00:33:33

Thanks Damian, that makes perfect sense.

It does, however, lead me to another problem in that it is still returning up to two position entries per employee rather than one single position per employee because the OR operator returns a row for each matching true condition. I would like it to only return the row for the first test, "PositionEndDate IS NULL", if it is true. I can't find a relevant logical operator in T-SQL. The relevant operator in Java is called a short-circuit boolean operator.

Could anybody help?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-19 : 00:50:53

Ahh of course..

When you have a NULL end date you don't want to go looking for previous positions, add in a little boolean logic .

SELECT EmployeeID, PositionID
FROM EMPOS p1
WHERE PositionEndDate IS NULL OR
( PositionEndDate IS NOT NULL AND
PositionEndDate = (SELECT MAX(p2.PositionEndDate)
FROM EMPOS p2
WHERE p1.EmployeeID = p2.EmployeeID)
)

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources