SQL Query to find Duplicate email under different ID [duplicate]

Question

I have a table email table which records the email_id against ind_ref(Unique).

I want to return only those IND_ref which shares the same email ID. in my example the query should return IND_ref 1212 and 1555 as both shares [email protected]. How can i achieve this? Any help would be much appriciated.

Yogesh Sharma · Accepted Answer · 2019-03-01 15:01:17Z

2

You can use exists :

select t.* from table t where exists (select 1 from table t1 where t1.email = t.email and t1.ind_ref <> t.ind_ref);

t.* indicates you are selecting all columns from table, if you want only limited columns then you can do :

select t.ind_ref, t.email . . .

edited Mar 1, 2019 at 15:01

answered Mar 1, 2019 at 14:48

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Biswa Over a year ago

you are a STAR. Thanks a lot. May i know why you used t.* on your select query and why it is used. I'm new to SQL and it found its quite new. Are you creating virtual temporary table without physically creating one? please explain your query

Eric Brandt Over a year ago

@Biswa, SELECT * FROM... and query result sets are fundamental SQL concepts. You should take one of the many SQL tutorials online to get an understanding of them. There are many to chose from.

Eric Brandt Over a year ago

Although, in fairness, if you select STAR from StackOverflow, @Yogesh is in the result set.

Biswa Over a year ago

@EricBrandt I know Select * from table t but never came across select t.* from table t what that (.*) means i didnt got.

Eric Brandt Over a year ago

@Biswa, the t is a table alias, a short way to refer to the objects in your query. Every table in your queries should have a meaningful, short alias, and then every column reference anywhere in your queries (select lists, join criteria, where clauses, etc.) should include the alias to make it clear which table every column comes from. Not a requirement, but a best practice.

|

Zaynul Abadin Tuhin · Accepted Answer · 2019-03-01 14:51:14Z

use aggregation

 select distinct ind_ref from table_name where email in ( select email from table_name group by email having count( *)>1 )

Collectives™ on Stack Overflow

SQL Query to find Duplicate email under different ID [duplicate]

2 Answers 2

7 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Linked

Related