Mysql Query Left Join error

Question

Hey guys a quick mysql problem ive got this current query whats giving me a headache

SELECT stafflist.id, stafflist.full_name, (wages.wage /5) * stafflist.avgworkingdays AS workingwage ,
count(sick.d) AS DaysOffSick, variables.value AS VAT, count(hols.d) AS Prebooked_Holidays,


((((wages.wage /5) * stafflist.avgworkingdays)/stafflist.avgworkingdaysmonth)*count(sick.d)) AS daysoffsickvalue,
((((((wages.wage / 5) * stafflist.avgworkingdays) * 3 ) /100) * (100 + variables.value))/ (stafflist.avgworkingdaysmonth)) * (stafflist.avgworkingdaysmonth 
- count(hols.d)) AS Commision_target_After_Holidays

from stafflist 

LEFT JOIN wages ON wages.wagesid = stafflist.WageGrade
LEFT JOIN variables ON variables.company=stafflist.company and variables.name = 'VAT'
LEFT JOIN off AS sick ON sick.StaffMember = stafflist.id and sick.isitsick=1 AND sick.addedtowages=0
LEFT JOIN off As hols ON hols.StaffMember = stafflist.id and hols.isitsick=0 AND hols.addedtowages=0 AND hols.m = MONTH(now()) -1 AND hols.prebooked=1
WHERE stafflist.id = 48
GROUP By stafflist.full_name

the problem is i want it to count how many days are marked as sick and how many as holidays however once i left join the off table again and name it as hols the field daysoffsick pulls the days marked as holidays

could you please help me out with this

regards Shane

Answer 1

Hey slowie from what I understand you just want a simple count of days off from sickness and days of for scheduled holiday grouped by employee. What you need to do is put a subquery in your left joins. I have provided a simpler table with data and part of the query you will need.

http://sqlfiddle.com/#!2/8a73c/13

it's up to you to plug the rest of your query together but I believe this is what you are after