I have spent the last 24hrs figuring how things will work. I have three table(employeeAttendance, employeeInfor, employeeExcuse) employeeAttendace this gives the record of all attendance made on that (EmployeeNo,Date) employeeInfor this gives the complete record of employee information (Name, Dept etc) employeeExcuse this gives a reason for absence when attendance is not upto requirement
SELECT employeeAttendance.EmployeeNo, COUNT( employeeAttendance.EmployeeNo ) AS `att`,CASE att when>20 then(select attreason from employeeExcuse) else 'explanation' END AS Remarks FROM employeeAttendance INNER JOIN employeeInfor ON employeeAttendance.EmployeeNo = employeeInfor.EmployeeNo GROUP BY employeeAttendance.attyear ORDER BY employeeAttendance.attmonth;
this code will give a *HINT*. My problem is i have joined employeeAttendance and employeeInfor, is working perfectly, the main issue is i want to join table employeeExcuse when employee attendance is less than requirement (let say 10) so as to check if he has an excuse days otherwise there need for an explanation *ERROR*
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>20 then(select att from employeeExcuse) else 'explanation' END AS Remarks' at line 1
Thank you for your help