-2

I'm having a little trouble figuring out this SQL statement. I have three tables employee, job, and job type.

job type is reference table. It has the name of the job, how long that job takes, and how much is charged for that job.

job has records of what job was performed for what client by which employee and the date performed. It links to job type jobname.

employee has name of employee, and employee id. It links to job table by employee id.

I need to display a list of all employees as well as the cheapest and most expensive jobs they did for a given year.

I think I will probably have to have an embedded select and a few joins. Maybe a unionThe complexity is just too far out of my experience. I'm not even sure where to being to be honest.

user2328273
  • 868
  • 3
  • 12
  • 22

1 Answers1

0

You should use the employee table as the primary table in your query so you will get one row per employee, even if that employee is associated to 0 jobs. Then use outer joins to the job and job_type tables, group by the employee identifiers, and use the min() and max() aggregate functions on the job table to select the min and max job costs (if any).

Something like this should help you get started:

select employee.employee_id ,
  employee.employee_name, 
  min(job_type.cost),
  max(job_type.cost)
from employee 
  left outer join job on job.employee_id = employee.employee_id
  left outer join job_type on job_type.job_type_id = job.job_type_id
group by employee.employee_id, 
  employee.employee_name
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Thanks. That helps a lot. I haven't had something that complex so I wasn't even sure where to begin with the logic. – user2328273 Jan 30 '17 at 20:55