Solved

Replace Group by and Having Clause with composite query

Posted on 2004-10-02
6
394 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
Comment Utility
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 30 total points
Comment Utility
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 40 total points
Comment Utility

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
Comment Utility
suggest 30 to me and 40 to izblank
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now