SQL question

What's the best way of finding duplicate data in a table. For instance, I have a 'player' table that contains football players with the fields: id, clubid, squadnumber.

Squadnumbers are non-sequential and can be anything depending on player preference or what is available when he joins the club.

Is there a way of quickly pulling up playerids who have a matching clubid AND squadnumber? Or do I have to loop through each player and check his squadnumber against those in the database?

maybe something like this :

select p1.id, p2.id
from player p1, player p2
where p1.clubid = p2.clubid
and p1.squadnumber = p2.squadnumber
and p1.id <> p2.id   -- only not the same player ;-)

Not sure what DBMS you want it in and exactly what your table structure is but adapt something like this:

Select
    *
From
    Player as P
Where
    (P.ClubId, P.SquadNumber) in
(   Select
        D.ClubId
       ,D.SquadNumber
    From
        Player as D
    Group by
        D.ClubId
       ,D.SquadNumber
    Having
        count(*) > 1
)
Order by
    P.ClubId
   ,P.SquadNumber
   ,P.Id
;

The 'inner' query returns the ClubId, SquadNumber pairs that have more than one occurrence in the Player table.

The 'outer' query then selects the rows that have those values so you can see the player ids and everything else.

Thanks guys. That's opened my eyes a bit. :)