List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 5 2000 5:07pm
Subject:Re: Syntax question
View as plain text  
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 )

Thread
Syntax questionBev Kennedy5 Apr
  • Re: Syntax questionsinisa5 Apr
  • Re: Syntax questionGerald Clark5 Apr