kokenge wrote:
This is such a simple SQL statement. So sorry for the question, but I can't
get it to work.
I'm trying to get a list of employees and the last time they worked on a
job.
FIles are.
employee file : with empl_num = employee number
job_history file : with empl_num, job_num, and last_date = last date the
employee worked on a job
Each employee has worked many jobs during his employment . so employee to
job_history is 1 to many
The sql is very simple and for some reason I keep getting a error saying the
it can't reference stuff in the subselect to the file in the Select????? I
have it working in all my other databases.
-----------------------------------------------------
SELECT *
FROM employee
JOIN job_history
ON job_history.empl_num = employee.empl_num
AND job_history.last_date = (SELECT max(j1.last_date)
FROM job_history as j1
WHERE j1.empl_num = employee.empl_num)
---------------------------------------------------------
Just to simple - so what am I doing wrong????
Thanks for the help..
Dan
Dan,
I'm not sure I understand your problem but this is what I would try
based on your description.
select employee_name, job_name, max(last_date) as last_date_on_job
from employee as e
left join job_history as j on j.empl_num = e.empl_num
group by e.empl_num, j.job_num;
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
