select duplicate rows without grouping?

Discussion in 'Databases' started by grobar, Dec 16, 2008.

0
  1. #1
    Hello all,

    I'm having trouble coming up with a sql statement (for ms access) that will select only rows where the value in "m_ip" exists in other rows.

    In the results I want to print out each of the instances, not just a count or something. (no grouping/aggregating)

    End result will show me all rows of a forum member table where the IP address used is ALSO used by another member row in the table.
     
    grobar, Dec 16, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    You would most likely need to use nested select statements.

    You may need to alter this for your db.

    

SELECT * FROM my_table WHERE id IN (

SELECT id FROM my_table GROUP BY id HAVING COUNT(*) > 1

)
    Code (markup):
    Edit: This may or may not work for you. I though I saw you were using MS SQL. Not sure how it will act in access.
     
    jestep, Dec 16, 2008 IP