SQL Query question over multiple tables
 

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

Re: SQL Query question over multiple tables
 

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

Re: SQL Query question over multiple tables
 

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

Re: SQL Query question over multiple tables
 

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;

Re: SQL Query question over multiple tables
 

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

Re: SQL Query question over multiple tables
 

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.