Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    105

    Unanswered: need a query.......

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •