0

I am trying to write a mysql query that will list all employees, their job function, and the total costs of all jobs they did for a particular day. Here is the relevant portion of my tables:

job_function table
emp_func
emp_num


employee table
emp_name
emp_num

job table
job_date
emp_num
job_name

job_type table
cost
job_name

The query works except that it is only returns the employees that had a job for that particular day. If an employee had no job that day they do not show up. I need to to display all employees, even those that had no job that day. Any suggestions on how to do that? Here is my query:

SELECT
employee.emp_name,
job_function,emp_func,
SUM(job_type.cost)
FROM
employee
INNER JOIN job ON job.emp_num = employee.emp_num
INNER JOIN job_function ON job_function.emp_num = employee.emp_num
INNER JOIN job_type ON job.job_name = job_type.job_name
where job_date = '2016-04-16'
group by emp_name;
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
user2328273
  • 868
  • 3
  • 12
  • 22
  • you should find your answer here. http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Adil Eleemrani Feb 02 '17 at 20:47
  • Is there anything more I need to change or it is just the join? I thought it might be a joining issue but I tried a few others and it didn't get me any closer. `FULL OUTER JOIN` doesn't seem to work with MYSQL either, at least not in the way I am using it. – user2328273 Feb 02 '17 at 21:08
  • It's also a bit confusing as I am joining 4 tables instead of 2. – user2328273 Feb 02 '17 at 21:26

2 Answers2

0

If you put job_date = '2016-04-16' you can see only the employees that work in that day, you should remove WHERE to see all the employees or put an interval like job_date>'2016-04-16' AND job_date<'2016-05-31'

Biagio Boi
  • 58
  • 6
  • Right, but I need the date. I realize the `where` clause is excluding those employees but I need to figure out how to include those employees and keep the date. That's just as close as I have gotten. It should show ALL employees and the total costs of their jobs for that day. If there are no jobs for an employee that day, it should show a sum of 0, instead of not including the employee altogether. – user2328273 Feb 02 '17 at 21:04
  • Wow, i'm thinking about your problem since 10 minutes without find a solution, it's a little bit difficul – Biagio Boi Feb 02 '17 at 21:09
  • Lol, glad it's not just me. – user2328273 Feb 02 '17 at 21:09
  • Maybe i've got a solution, you should include all attributes without 'WHERE' but you should modifier a SUM() with a condition here – Biagio Boi Feb 02 '17 at 21:12
0

You need a left join to get results even if there are no rows in the left joined table that fit the on-condition; the values in columns of the left joined tables are returned as null in such cases.

You also need to move your where-condition to the on-condition; your where would otherwise require the date in the job-table to be 2016-04-16, so it has to exist (and not be null), which would make the left join behave like a join again.

SELECT 
  employee.emp_name,
  job_function.emp_func,
  SUM(job_type.cost)
FROM employee
JOIN job_function ON job_function.emp_num = employee.emp_num
left JOIN job ON job.emp_num = employee.emp_num and job.job_date = '2016-04-16'    
left JOIN job_type ON job.job_name = job_type.job_name
group by employee.emp_name, job_function.emp_func;

I also fixed your group by and added the emp_func-column there, since you select that column. This would otherwise result in an error in MySQL 5.7+ with default settings, and otherwise might give you wrong results in cases where an employee has more than one function.

Solarflare
  • 10,721
  • 2
  • 18
  • 35