Need help with data arrays

From your two subqueries, I think you only need the SUM() fields, a.total_absence and s.total_sickness.

I've tried leaving out the events.eventid in the subquery select but that throws the following error:

Code:

Error Number: 1054

Unknown column 'events.staffid' in 'on clause'

SELECT * FROM staff t LEFT JOIN ( SELECT COALESCE(events.staffid, 0), COALESCE(SUM(amount),0) AS total_absence FROM events WHERE events.typeid = 1 and events.date between '2015-03-12' and '2016-03-11' GROUP BY events.staffid ) a ON t.staffid = events.staffid LEFT JOIN ( SELECT events.staffid, SUM(amount) total_sickness FROM events WHERE events.typeid = 2 and events.date between '2015-03-12' and '2016-03-11' GROUP BY events.staffid ) s ON t.staffid = s.staffid JOIN department ON t.deptid = department.deptid

so it seems to need the fields explicitly selected so it can do the join maybe? It's no big deal though, I can live with having the extra data in the result set.

Just another point to note ... I've found that specifying the COALESCE like this, with the closing bracket after the AS variable:

Code:

COALESCE(SUM(amount) AS total_absence, 0)

throws an error. Whilst specifying it like this (with closing brackets after the SUM(amount))

Code:

COALESCE(SUM(amount),0) AS total_absence

works just fine.

Thanks for your help, I learned a lot too. I'll pick up a copy of that book too!