Display entries associated with two identifiers

Question

I have a table

ID NAME -------- 1 AAA 2 BBB 2 AAA 2 CCC 1 DDD 2 DDD 

I need to display entries that are associated with both identifiers 1 and 2

 NAME ---- AAA DDD 

I use the below query -

 Select Name from table1 where ID IN (1,2); 

But he shows me -

 NAME ----- AAA BBB CCC DDD 

How can I change my request to solve this problem?

Answer 1

 SELECT DISTINCT NAME FROM tabel1 t1 join table1 t2 on t1.id = 1 and t2.id = 2 and t1.name = t2.name 

or there may be many matches

 SELECT DISTINCT NAME FROM tabel1 t1 WHERE EXISTS (SELECT 1 FROM table1 t2 WHERE t1.name = t2.name and t2.id = 2) and t1.id = 1 

or

 SELECT NAME FROM tabel1 WHERE id = 1 INTERSECT SELECT NAME FROM tabel1 WHERE id = 2 

Answer 2

You need to group by name and then count the individual identifiers that you want to filter.

 select name from table where id in (1,2) group by name having count (distinct ID) = 2 

Answer 3

 Select Name from table1 where ID IN (1,2) and Name in ( select Name from table1 where ID IN (1,2) group by Name having count(id) =2 ) ; 

Answer 4

 Select Name from TableName where id in (1,2) group by Name having Count(Distinct Id)>1 

Answer 5

 select name from table t where id = 1 and exists (select 1 from table where name = t.name and id = 2)