Yes your solution will get you the last date they worked on a job. Probalm is there is other information in the job record I need. So if I select other stuff from the job record it may not be from the last job record. For example - Using this method if I select the MIN(job date) and salary on that job, I may get the same salary if I selected the MAX(job date). So not only do I need the last time he worked on the job, I also need the complete information from that job_history record. Thanks for the help... Have a great day. Dan
Dennis Cote wrote: > > 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] > ----------------------------------------------------------------------------- > > > -- View this message in context: http://www.nabble.com/subselect-tf3327306.html#a9253538 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
