Display records linked with two IDs

Question

I have a table

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

I have to display records which are linked with both ID 1 and 2

NAME
----
AAA
DDD

I am using below query -

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

But it is displaying me -

NAME
-----
AAA
BBB
CCC
DDD

How do I change my query 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 if there can 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 the name, then count the distinct IDs that you wish to filter by.

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