Mysql joins 3 tables with multiple fields <p>I am trying to join an employee table "employees", a timeclock transaction table "transactions" and another table "hta" with a time field. I want the timeclock table to add up the time the employee worked between a set of days. I want the other table "hta" to add up the time for each employee between a set of days and look to the employee table to get the employees name. To make things simple right now I am only working with one day. Therefore there is only one entry per employee. No one has clocked in multiple times this day. In the hta table there are multiple entries per person for the day. </p> <p>Here is my code</p> <pre><code>SELECT TIME_FORMAT( SEC_TO_TIME( SUM( TIME_TO_SEC( `t`.`hours_today` ) ) ) , "%H:%i:%s" ) AS `hours_today` , TIME_FORMAT( SEC_TO_TIME( SUM( TIME_TO_SEC( `h`.`run_time` ) ) ) , "%H:%i:%s" ) AS `run_time` , `t`.`employee_id` , `e`.`employee_id` , `e`.`displayname` FROM `transactions` t LEFT JOIN `hta` h ON `t`.`employee_id` = `h`.`employee_id` AND `t`.`date` = `h`.`date` LEFT JOIN `employees` e on `t`.`employee_id` = `e`.`employee_id` WHERE `t`.`date` &gt;= "2015-08-14" AND `t`.`date` &lt;= "2015-08-14" GROUP BY `t`.`employee_id` ORDER BY `t`.`employee_id` ASC </code></pre> <p>The output I am getting is hours_today is wrong, run_time is correct, employee_id is correct, and display name is correct.<br> On employee 1 hours_today should be 05:48:00. Remember the employee only has one entry in the hours_today column for that date range. However the mysql output for this employee is 29:00:00. So it looks as if the date for the timeclock table is not being limited like it is for the hta table. Employee 2 hours_today should be 09:34:00 but the output from the query is 95:40:00</p> <p>I have tried more detailed code such as </p> <pre><code>SELECT TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(t.hours_today))), "%H:%i:%s") as hours_today, t.employee_id, e.employee_id, e.displayname FROM transactions t LEFT JOIN employees e ON t.employee_id = e.employee_id LEFT JOIN (SELECT employee_id, TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(run_time))), "%H:%i:%s") as run_time FROM hta WHERE DATE(date_time) &gt;= "2015-08-14" AND DATE(date_time) &lt;= "2015-08-14" GROUP BY employee_id) hta on e.employee_id = hta.employee_id LEFT JOIN hta h on hta.employee_id = h.employee_id WHERE date &gt;= "2015-08-14" AND date &lt;= "2015-08-14" GROUP BY t.employee_id ORDER BY t.employee_id ASC </code></pre> <p>and </p> <pre><code>SELECT TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(t.hours_today))), "%H:%i:%s") as hours_today, TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(h.run_time))), "%H:%i:%s") as run_time, t.employee_id, e.employee_id, e.displayname FROM transactions t LEFT JOIN employees e ON t.employee_id = e.employee_id LEFT JOIN hta h on e.employee_id = h.employee_id and t.date = DATE(h.date_time) WHERE date &gt;= "2015-08-14" AND date &lt;= "2015-08-14" GROUP BY t.employee_id ORDER BY t.employee_id ASC </code></pre>