Question Re: Get two fields from a subquery in Technology Q&A

Get two fields from a subquery

BudDurland — Wed, 28 Jun 2017 09:10:29 GMT

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.

Re: Get two fields from a subquery

VolkerBarth — Wed, 28 Jun 2017 10:20:35 GMT

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

Re: Get two fields from a subquery

thomas_duemesnil — Thu, 29 Jun 2017 03:00:56 GMT

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

Re: Get two fields from a subquery

fvestjens — Thu, 29 Jun 2017 03:14:00 GMT

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)

Re: Get two fields from a subquery

BudDurland — Wed, 05 Jul 2017 10:40:19 GMT

Works like a charm! Many thanks.