Hi all,

I have a table which have the following data's for ex:
C:\Documents and Settings\Administrator>db2 select * from m1

EMPNO EMPNAME DEPT
----------- ---------- --------------------
1 meens db2
2 kapli db2
3 mini sql
4 jeeva sql
5 sam sql
6 ram sql

6 record(s) selected.
I need to query empno,empname where the dept is having atleast 3 employess.For ex. the query should return only the sql dept which is having 4 employees from the above table.

Any help....

Thanks and Regards
Meena.s

try this:

SELECT EMPNO, EMPNAME
FROM m1
WHERE DEPT IN (
SELECT DEPT FROM m1
GROUP BY DEPT
HAVING COUNT(*) >= 3 )

or try this:

SELECT EMPNO , EMPNAME
FROM m1 a
INNER JOIN
( SELECT DEPT
FROM m1
GROUP BY DEPT
HAVING COUNT(*) >= 3 ) b
ON a.DEPT = b.DEPT