Get two fields from a subquery

[BudDurland]

This could easily be my lack of knowledge about SQL syntax, but the SQL/Anywhere 16 docs, and Google haven't yielded much happiness

Consider (and please forgive typos, as I'm shooting from the hip):

Create Table JobList (
   JobNumber  int not null autoincrement,
   SkillLevel int not null,
   Status varchar(10)
   )

Create Table Employees (
   id   Int not null autoincrement,
   FirstName   varchar(30),
   LastName    varchar(50),
   LastWorked  datetime,
   SkillLevel  int
   )

Now, for every job in status 'open', I want the first and last name of the employee who hasn't worked the longest, and has the matching SkillLevel. So this would work:

select job.jobID as TheJob, job.SkillLevel As Skill,
(select top 1 firstname from Employees where skilllevel = skill order by LastWorked) as FName,
(select top 1 lastname from Employees where skilllevel = skill order by LastWorked) as LName
from job where job.status = 'Open'

It seems some variation of this should work, but I haven't got it yet:

select job.jobID as TheJob, job.SkillLevel As Skill,
e1.FirstName, e1.Lastname, e1.SkillLevel
from job, 
  (select top 1 employees.FirstName, Employees.lastname, Employees.SkillLevel order by LastWorked) as e1
 where job.status = 'Open'
and e1.SkillLevel = jobs.SkillLevel

Any guidance appreciated.

[VolkerBarth]

I guess the derived table misses a group by SkillLevel, because you want the according "most relaxed" employee per skill level...

[thomas_duemesnil · Accepted solution]

Take a look in outer apply

select job.jobID as TheJob, job.SkillLevel As Skill, e1.FirstName, e1.Lastname
from job
outer apply (select top 1 employees.FirstName, Employees.lastname where Employees.SkillLevel = jobs.SkillLevel order by LastWorked) as e1
where job.status = 'Open'

Other possibility would be a WITH clause.

HTH

[BudDurland · Accepted solution]

Works like a charm! Many thanks.

[fvestjens]

This should work:

select
    job.jobID as TheJob, job.SkillLevel As Skill,
    e1.FirstName, e1.Lastname, e1.SkillLevel
from
    job, Employees e1
where
    job.status = 'Open' and
    e1.Id = (select top 1 Id from Employees where SkillLevel = job.Skill order by lastWorked)