Results 1 to 4 of 4

Thread: sql query

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: sql query

    SELECT first_name,COUNT(*)
    INTO #temp1
    FROM emp
    GROUP BY first_name
    HAVING COUNT(*)>1

    SELECT * FROM emp WHERE first_name IN(SELECT first_name FROM #temp1)

    How can i combine these two sql statements into one,and can i avoaid the temp table during this process?

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table emp
    create table emp(first_name varchar(10))
    go
    insert emp values('Tom')
    insert emp values('Tom')
    insert emp values('Ben')
    insert emp values('Brett')
    go
    SELECT * FROM emp
    WHERE first_name IN(SELECT first_name FROM emp
    GROUP BY first_name
    HAVING COUNT(*)>1
    )

  3. #3
    Join Date
    Oct 2003
    Posts
    84
    Doesn't your first query get the desired results after you remove the #temp1 clause?

    SELECT first_name,COUNT(*)
    FROM emp
    GROUP BY first_name
    HAVING COUNT(*)>1

    If it's cause you don't want to return COUNT(*) in your recordset, try this:

    SELECT first_name
    FROM emp
    GROUP BY first_name
    HAVING COUNT(*) > 1;


    that should do it.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The argument of IN() vs a joined subquery comes up on this forum every so often, so I won't go into it here. This is my preferred method:

    SELECT *
    FROM emp
    Inner join
    (SELECT first_name FROM emp GROUP BY first_name HAVING COUNT(*)>1) DupNames on emp.first_name = DupNames.first_name
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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