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?
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With