Combine Three MySQL Queries Into One Using JOIN

Question

I'm having a lot of trouble combining two MySQL queries and getting the correct data.

My first query 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, on each employee result I execute three queries to get specific information about each employee:

Firstly, to get the total minutes they've worked, I execute the following query. When I try to join this one with the first one, I'm having problems returning all of the minutes they've worked regardless of whether they've made a sale on a particular 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 job 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 lastly I get an average weighting by which to multiply sales values based on the days and times of days they've 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`

Could anyone provide me with guidance on whether this is possible at all, and if so, where to start with it?

Thanks in advance!

Answer 1

The first and third queries are easy to join. You just write a subquery that returns the results grouped for each employee ID, and join those 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 middle query is harder. There's probably a way to write it as a single query that returns the top job grouped by employee, but I can't think of it now.