SQL Query question over multiple tables

[djclipz]

Hey all,

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;

which gives

MAX(COUNT(*))
-------------
            4

But that doesn't help much as i have nothing about which employeeid it corresponds to! Just the highest value!

Thank-you
DJ CLipz

[debasisdas]

also find out the employee id and pas that employee id to outer query to find out all the other details.

[Nige Ridd]

Not the best solution but...

SELECT * FROM EMPLOYEE
WHERE EMPLOYEEID IN (
SELECT EMPLOYEEID FROM ABSENCEHISTORY GROUP BY EMPLOYEEID HAVING count(*) = ALL(SELECT count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID))

You should take into account that there may be more than one person with the worst attendance.
Nige

[RDWilson2]

Not the best solution but...

SELECT * FROM EMPLOYEE
WHERE EMPLOYEEID IN (
SELECT EMPLOYEEID FROM ABSENCEHISTORY GROUP BY EMPLOYEEID HAVING count(*) = ALL(SELECT count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID))

You should take into account that there may be more than one person with the worst attendance.
Nige

How about a slight modification on that theme:

SELECT Z.Absences, E.* FROM EMPLOYEE
WHERE EMPLOYEEID E
INNER JOIN 
(
 SELECT TOP 1 EMPLOYEEID, Absences
 FROM 
 (
  SELECT EMPLOYEEID,  COUNT(EMPLOYEEID) AS Absences 
  FROM ABSENCEHISTORY 
 GROUP BY EMPLOYEEID
 ) Y 
 ORDER BY Absences, EMPLOYEEID
) Z
ON E.EMPLOYEEID=Z.EMPLOYEEID;

[Nige Ridd]

Unfortunately Select top n isn't supported by Oracle.
Nige

[RDWilson2]

Unfortunately Select top n isn't supported by Oracle.
Nige

Nige,

Sorry about that. I am not currently working with Oracle but, rather, with SQL Server (and my next most recent experience is on Teradata which also supports the TOP n feature) so I am not sure how to immulate the TOP n functionality in Oracle. I am, however, quite sure that there is a means for doing so.