Distinct Query to find duplicates (diff primary key)

[Vexorg]

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION><TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>Hi,
Im having trouble making a query that can find the duplicates like below. The problem is that they have different primary keys, but they have the same fields else. My only guess for this was something similar to

Select DISTINCT core.[Entity ID], core.[First], core.[Last], core.[Email1] from Table1

Where (((core.Email1)>"1")) 

Table1



</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Entity ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>First</TH><TH borderColor=#000000 bgColor=#c0c0c0>Last</TH><TH borderColor=#000000 bgColor=#c0c0c0>Email</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1000</TD><TD borderColor=#d0d7e5>John</TD><TD borderColor=#d0d7e5>Smith</TD><TD borderColor=#d0d7e5>jsmith1@gmail.com</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2000</TD><TD borderColor=#d0d7e5>John</TD><TD borderColor=#d0d7e5>Smith</TD><TD borderColor=#d0d7e5>jsmith1@gmail.com</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>3000</TD><TD borderColor=#d0d7e5>Sally</TD><TD borderColor=#d0d7e5>Mae</TD><TD borderColor=#d0d7e5>sm@asdf.com</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>4000</TD><TD borderColor=#d0d7e5>Howard</TD><TD borderColor=#d0d7e5>H</TD><TD borderColor=#d0d7e5>adfasd@adfs.com</TD></TR></TBODY><TFOOT></TFOOT></TABLE></I>

I would like the answers to return:


Table1



</CAPTION></I><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>Entity ID</TH><TH borderColor=#000000 bgColor=#c0c0c0>First</TH><TH borderColor=#000000 bgColor=#c0c0c0>Last</TH><TH borderColor=#000000 bgColor=#c0c0c0>Email</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>1000</TD><TD borderColor=#d0d7e5>John</TD><TD borderColor=#d0d7e5>Smith</TD><TD borderColor=#d0d7e5>jsmith1@gmail.com</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>2000</TD><TD borderColor=#d0d7e5>John</TD><TD borderColor=#d0d7e5>Smith</TD><TD borderColor=#d0d7e5>jsmith1@gmail.com</TD></TR></TBODY><TFOOT></TFOOT></TABLE>


Can anyone help? Sorry for the weird formatting!

 

[james_lankford]

select 
  core.*
from
  core
inner join 
  (
    select 
      core.Email
    from
      core 
    group by 
      core.Email
    having 
      count (*) > 1
  ) as dups
  on 
    core.email = dups.email

 

[Vexorg]

select 
  core.*
from
  core
inner join 
  (
    select 
      core.Email
    from
      core 
    group by 
      core.Email
    having 
      count (*) > 1
  ) as dups
  on 
    core.email = dups.email

Hmm it keeps asking me to enter a parameter for dups.email

 

[james_lankford]

is the name of your field email or email1 ?

dups is just an alias for a subquery

you could just as easily take out the

   select 
      core.Email
    from
      core 
    group by 
      core.Email
    having 
      count (*) > 1

save it as its own query, call it dups
and then make a 2nd query which joins to the first query

select 
  core.*
from
  core
inner join 
  dups
  on 
    core.email = dups.email

but instead of making 2 queries, I just like to put it all together

 

[Vexorg]

It worked, thanks!