Bev Kennedy wrote:
> I have puzzled over this for some time, and searched the manual. How
> can I display only those records that are duplicates in a field. The
> table is:
>
> Field Type Null
> userid char(8) No
> ssn char(10) No
>
> Indexes are:
>
> Keyname Unique Field
> userid No userid
> ssn No ssn
>
> Current Query is:
>
> select x.ssn, y.userid, count(*)
> from u_index as x, u_index as y
> where y.ssn = x.ssn
> group by x.ssn, y.userid;
>
> This shows every line (23,000). I only want to see the duplicates or
> where count(*) is greater than 1.
>
> Any help will be appreciated.
>
You have a couple of problems here.
You have no primary key to check to see if the x and y records are the same
record, and you
forgot to check for it in the where clause.
You should have:
where ( y.ssn = x.ssn ) and ( y.userid != x.userid )
