Combine three MySQL queries into one using JOIN

Question

I'm having trouble merging two MySQL queries and getting the right data.

My first request is as follows:

SELECT e.employee_id, e.employee_name, COUNT(s.sale_id) AS employee_sales FROM employees e, sales s, days d WHERE s.sale_id = '$sale_type' AND d.day_year_id = '$year' AND s.sale_day_id = d.day_id AND e.employee_id = s.sale_employee_id GROUP BY e.employee_id 

Then, for each employee result, I perform three queries to obtain specific information about each employee:

First, to get the total number of minutes they worked with, I execute the following query. When I try to join this with the first one, I have problems returning all the minutes that they worked on, regardless of whether they made a sale on a specific day:

 SELECT SUM(employee_day_end_minute-employee_day_start_minute) AS employee_minutes FROM employee_days ed, days d WHERE ed.employee_days_employee_id = '$employee_id' AND ed.employee_days_day_id = d.day_id AND d.day_year_id = '$year' 

Secondly, to get the type of assignment that they performed the most:

 SELECT ed.employee_day_position, COUNT(ed.employee_day_position) AS count FROM employee_days ed, days d WHERE ed.employee_days_employee_id = '$employee_id' AND ed.employee_days_day_id = d.day_id AND d.day_year_id = '$year' GROUP BY match_player_position ORDER BY count DESC LIMIT 1 

And finally, I get the average weight to multiply the sales values ​​based on the days and days of the days on which they worked:

 SELECT (SUM(dw.day_weighting_value)/COUNT(s.day_weighting_value)) AS employee_weigting FROM employee_days ed, day_weightings dw, days d WHERE ed.employee_day_employee_id = '$employee_id' AND ed.employee_day_day_id = d.day_id AND d.day_year_id = '$year' AND dw.day_weighting_day_id = d.day_id AND dw.day_weighting_minute >= ed.employee_day_start_minute AND dw.day_weighting_minute <= ed.employee_day_end_minute` 

Can someone give me guidance on whether this is possible at all, and if so, where to start?

Thanks in advance!

Answer 1

The first and third queries are easily combined. You simply write a subquery that returns the results grouped for each employee identifier, and attach these subqueries to the original query.

 SELECT p.employee_id, e.employee_name, COUNT(s.sale_id) AS employee_sales, ed.employee_minutes, edw.employee_weighting FROM employees e JOIN sales s ON e.employee_id = s.sale_employee_id JOIN days d ON s.sale_day_id = d.day_id JOIN (SELECT ed.employee_days_employee_id, SUM(employee_day_end_minute-employee_day_start_minute) AS employee_minutes FROM employee_days ed JOIN days d ON ed.employee_days_day_id = d.day_id WHERE d.day_year_id = '$year' GROUP BY ed.employee_days_employee_id) AS ed ON ed.employee_days_employee_id = e.employee_id JOIN (SELECT ed.employee_days_employee_id, (SUM(dw.day_weighting_value)/COUNT(s.day_weighting_value)) AS employee_weigting FROM employee_days ed JOIN day_weightings dw ON dw.day_weighting_minute >= ed.employee_day_start_minute AND dw.day_weighting_minute <= ed.employee_day_end_minute days d ON dw.day_weighting_day_id = d.day_id AND ed.employee_day_day_id = d.day_id WHERE d.day_year_id = '$year' GROUP BY ed.employee_days_employee_id) AS edw ON edw.employee_days_employee_id = e.employee_id WHERE s.sale_id = '$sale_type' AND d.day_year_id = '$year' GROUP BY e.employee_id 

The average query is more complicated. There is probably a way to write it as a single request that returns the top job grouped by an employee, but I can't think about it now.