0

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

enter image description here

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 abc@yahoo.com. How can i achieve this? Any help would be much appriciated.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Biswa
  • 331
  • 6
  • 22

2 Answers2

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
. . .
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • 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 – Biswa Mar 01 '19 at 14:55
  • @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 Mar 01 '19 at 15:01
  • Although, in fairness, if you `select STAR from StackOverflow`, @Yogesh is in the result set. – Eric Brandt Mar 01 '19 at 15:03
  • @EricBrandt I know Select * from table t but never came across select t.* from table t what that (.*) means i didnt got. – Biswa Mar 01 '19 at 15:04
  • @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. – Eric Brandt Mar 01 '19 at 15:47
  • For a good article on effective table aliases, here are Aaron Bertrand's thoughts on it: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 – Eric Brandt Mar 01 '19 at 15:50
  • @EricBrandt aha , table alias, I got you now. Yes select t.* from email t means select all the column from the table t. Got it. Thanks any ways bro.. – Biswa Mar 01 '19 at 16:06
1

use aggregation

  select distinct ind_ref
 from table_name where email in (  select email from table_name
    group by email 
    having count( *)>1
   )
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63