Join tables with SUM issue in MYSQL

by

When you use multiple joins in the main query, you end up with a cross product of all the tables, so the sums get multiplied by the number of rows matching in another table. You need to move the sums into subqueries.

SELECT last_name, first_name, DATE_FORMAT(LEAST(mil_date, tm_date),  '%m/%d/%y' ) AS dates, 
        total, minutes
FROM bhds_teachers AS i
LEFT JOIN (
    SELECT ds_id, YEARWEEK(mil_date) AS week, MIN(mil_date) AS mil_date, SUM(drive_time) AS minutes
    FROM bhds_mileage
    WHERE mil_date BETWEEN '2016-04-11' AND  '2016-04-30'
    AND bhds_mileage.ds_id = 5
    GROUP BY ds_id, week) AS m 
ON m.ds_id = i.ds_id
LEFT JOIN (
    SELECT ds_id, YEARWEEK(tm_date) AS week, MIN(tm_date) AS tm_date, SUM(tm_hours) AS total
    WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
    GROUP BY ds_id, week) AS t 
ON t.ds_id = i.ds_id AND t.week = m.week

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. 1052: Column ‘id’ in field list is ambiguous
  3. Join between tables in two different databases?
  4. Required to join 2 tables with their FKs in a 3rd table
  5. How can I join multiple SQL tables using the IDs?
  6. Why does MySQL report a syntax error on FULL OUTER JOIN?
  7. Mixing ANSI 1992 JOINs and COMMAs in a query
  8. Cumulative sum over a set of rows in mysql
  9. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  10. MySQL JOIN the most recent row only?
  11. How to find non-existing data from another Table by JOIN?
  12. MySQL Inner Join Query Multiple Tables
  13. Difference between these two joining table approaches?
  14. Laravel join with 3 Tables
  15. MySQL LEFT JOIN 3 tables
  16. how to use a like with a join in sql?
  17. When to use a left outer join?
  18. In what order are MySQL JOINs evaluated?
  19. How to store a one to many relation in MySQL database?
  20. MySQL Multiple Joins in one query?
  21. MYSQL OR vs IN performance
  22. SELECTING with multiple WHERE conditions on same column
  23. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  24. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  25. Cannot delete or update a parent row: a foreign key constraint fails
  26. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  27. OUTPUT Clause in MySQL
  28. Sorting by date & time in descending order?
  29. MySQL, Concatenate two columns
  30. How to select data where a field has a min value in MySQL?

Leave a Comment