QUERY HELP JOIN TABLES

[stivenbr]

Hello Everyone, 

 

I am having some trouble with a query and hopefully someone can guide me through this? I think this should be possible to accomplish. I have two tables. Table A stores hours and Table B stores driving time. I am trying to get all the people who have more than 6 hours worked in a day which I was able to do. But I also need to get the driving time for that specific day where they did more than 6 hours in a day. I hope I make sense. This is the query that I am using right now and is working to get everyone who worked more than 6 hours a day I just can get the minutes as well. The driving time is store in table B called ies_mileage and the field is duration. I have tried several things with no success. 

SELECT wk_date,full_name, SUM(hours) AS HOURS FROM `ies_weekly` LEFT JOIN infant_specialist ON ies_weekly.is_id = infant_specialist.is_id WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31' GROUP BY DAY(wk_date), ies_weekly.is_id HAVING SUM(hours) > 6
ORDER BY full_name  ASC

This is working, when I tried to add the duration the total gets messed up and it's no longer accurate. here is what i've tried 

SELECT wk_date,full_name, SUM(hours) AS HOURS, SUM(duration) AS minutes FROM `ies_weekly` LEFT JOIN infant_specialist ON ies_weekly.is_id = infant_specialist.is_id LEFT JOIN ies_mileage ON ies_weekly.wk_date = ies_mileage.mil_date AND ies_weekly.is_id = ies_mileage.ies_id WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31' GROUP BY DAY(wk_date), ies_weekly.is_id HAVING SUM(hours) > 6
ORDER BY full_name  ASC 

Thank you for your help