I have 3 tables, users (user info), mghours (volunteer time), mgtraining (training time). I want a result that lists some user columns, sum of volunteer hours and sum of two types of training:
uid lastname firstname mgstatus trainingyear vol at ce
I use the following and as long as I have the two fields from the mgtraining table and the LEFT OUTER JOIN for those in the query, I get skewed results. If I take that out of the query, my user info and vol columns are fine.
SELECT users.uid AS 'uid', users.lastname AS 'lastname', users.firstname AS 'firstname', users.mgstatus AS 'mgstatus', users.trainingyear AS 'trainingyear', sum(mghours.hourstime) AS 'vol', SUM(IF(mgtraining.trainingtype = 'AT', mgtraining.trainingtime, 0)) AS 'at', SUM(IF(mgtraining.trainingtype = 'CE', mgtraining.trainingtime, 0)) AS 'ce' FROM users LEFT OUTER JOIN mghours ON users.uid = mghours.uid AND year(mghours.hoursdate) = YEAR(CURDATE()) LEFT OUTER JOIN mgtraining ON users.uid = mgtraining.uid AND year(mgtraining.trainingdate) = YEAR(CURDATE()) WHERE users.coid = 33 GROUP BY users.uid ORDER BY users.lastname ASC
The users.coid = 33 references a county ID.
I've been pouring over this for hours. What am I doing wrong?