Ok first my problem. I have a database that has an employee table and then also an absent table(for when they are off sick).
The problem i have is using sql to find the employee that is off the most! So for my sql on the absent table i do something like this
select EMPLOYEEID, count(*) from ABSENCEHISTORY group by EMPLOYEEID having count(*)>= ALL(select count(*) from ABSENCEHISTORY group by EMPLOYEEID);
and this gives
EMPLOYEEID COUNT(*) ---------- ---------- 2 4
Now this is all good as i now know that the employee with id 2 has had the most absences. However is it possible in 1 query to use the EMPLOYEEID i have been given there to find out the attributes of the employee that are stored in the EMPLOYEE table. I have been trying JOINS, UNIONS but no success as far. Does anyone know if it is actually possible to do this in 1 query?
I have also tried this
select max(count(*)) from ABSENCEHISTORY group by EMPLOYEEID;
MAX(COUNT(*)) ------------- 4
But that doesn't help much as i have nothing about which employeeid it corresponds to! Just the highest value!