How to get partid that have two category and exist on 2000,2200 ?

Question

How to get partid that have two category for same part and exist on 2000,2200 ?
I work on sql server 2012 i face issue i can't get partid that have two category for same part
and these two category must be on category 2000 and 2200

as partid 1246 it have two category for same part id
and also thesetwo category 2000 and 2200
so How to make select query do that please ?

create table #category
(
PartId int,
Category int
)
insert into #category(PartId,Category)
values
(1246,2000),
(1246,2200),
(1250,2000),
(1250,2200),
(1290,2000),
(1350,2200),
(4000,3000),
(4000,5000)

what i try :

select partid,Category from #category  
where category in (2000,2200)  
group by partid  
having count(partid)=2  

expected result

PartId Category
1246 2000
1246 2200
1250 2000
1250 2200

Answer 1

Try this:

SELECT *
FROM #category AS c
WHERE EXISTS (
    SELECT PartId 
    FROM #category 
    WHERE PartId = c.PartId AND Category IN (2000, 2200) 
    GROUP BY PartId 
    HAVING COUNT(*) = 2
);