Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Replace Group by and Having Clause with composite query

Posted on 2004-10-02
6
Medium Priority
?
412 Views
Last Modified: 2010-04-27
Hi, I was asked to do a stupid job that need to find the staff no and office no of a staff that has 5 or more job on hand.  I can find the result by a simple query as below, but I am asked not to use simple query to do this.  ie. replacing the Group by or Having clause with subquery.  Actually, I don't know how to do so.  Can anyone help?

select staff_no, office_no, count(job_id)
from staff
group by staff_no, office_no
HAVING count(job_id) >= 5
;
0
Comment
Question by:vanvancela
  • 2
6 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12206934
You have to get a count by staff and office, and group by/having is the correct way to do it. Anything else will cause unnecessarily worse performance.
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 90 total points
ID: 12207502
your question says you don't need the actual number of jobs on hand

create table tmp1 as SELECT s2.job_id, count(*) as num FROM staff s2

select s1.staff_no, s1.office_no
from staff s1, tmp1 t
WHERE s1.job_id = t.job_id AND num > 5

0
 
LVL 6

Assisted Solution

by:izblank
izblank earned 120 total points
ID: 12214536

select distinct staff_no, office_no
from staff s1
where (select count(distinct job_id) from staff s2
            where s2.staff_no=s1.staff_no
            ) >5
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 12719426
suggest 30 to me and 40 to izblank
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question