SQL Query question over multiple tables

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 426,590 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,677 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser: Programming Forums

Thread Tools Display Modes

Views: 2806 | Replies: 5

•

Join Date: Apr 2008

Posts: 3

Reputation: djclipz is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

djclipz

Offline

Newbie Poster

SQL Query question over multiple tables

Apr 17th, 2008

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT EMPLOYEEID, count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID HAVING count(*)>= ALL(SELECT count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID);
select EMPLOYEEID, count(*) from ABSENCEHISTORY group by EMPLOYEEID having count(*)>= ALL(select count(*) from ABSENCEHISTORY group by EMPLOYEEID);

and this gives

Help with Code Tags

(Toggle Plain Text)

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT max(count(*)) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID;
select max(count(*)) from ABSENCEHISTORY group by EMPLOYEEID;

which gives

Help with Code Tags

(Toggle Plain Text)

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

Last edited by peter_budo : Apr 17th, 2008 at 7:23 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Feb 2007

Location: Bangalore,India

Posts: 1,290

Reputation: debasisdas is on a distinguished road

Rep Power: 4

Solved Threads: 83

debasisdas debasisdas is offline

Offline

Nearly a Posting Virtuoso

Re: SQL Query question over multiple tables

Apr 21st, 2008

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

Share your Knowledge.

•

Join Date: Nov 2007

Posts: 51

Reputation: Nige Ridd is an unknown quantity at this point

Rep Power: 1

Solved Threads: 9

Nige Ridd

Offline

Junior Poster in Training

Re: SQL Query question over multiple tables

Apr 23rd, 2008

Not the best solution but...

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM EMPLOYEE
WHERE EMPLOYEEID IN (
SELECT EMPLOYEEID FROM ABSENCEHISTORY GROUP BY EMPLOYEEID HAVING count(*) = ALL(SELECT count(*) FROM ABSENCEHISTORY GROUP BY EMPLOYEEID))
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

Last edited by peter_budo : Apr 23rd, 2008 at 7:01 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Nov 2007

Posts: 15

Reputation: RDWilson2 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

RDWilson2

Offline

Newbie Poster

Re: SQL Query question over multiple tables

Apr 23rd, 2008

•

Originally Posted by 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

How about a slight modification on that theme:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
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;

Last edited by peter_budo : Apr 23rd, 2008 at 7:02 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Nov 2007

Posts: 51

Reputation: Nige Ridd is an unknown quantity at this point

Rep Power: 1

Solved Threads: 9

Nige Ridd

Offline

Junior Poster in Training

Re: SQL Query question over multiple tables

Apr 23rd, 2008

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

•

Join Date: Nov 2007

Posts: 15

Reputation: RDWilson2 is an unknown quantity at this point

Rep Power: 1

Solved Threads: 0

RDWilson2

Offline

Newbie Poster

Re: SQL Query question over multiple tables

Apr 23rd, 2008

•

Originally Posted by Nige Ridd

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.