Solved

Replace Group by and Having Clause with composite query

Posted on 2004-10-02
6
401 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30 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 40 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I Test SQL in Filemaker 13? 3 204
FM - Sorting Titles 5 73
Filemaker Go 14 - Database Sync 4 354
filemaker security 1 705
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…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

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