SQLTeam.com | Weblogs | Forums

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

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 these two 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

77917-image.png

hi hope this helps

select * from #category a where exists 
(
	select partid,count(*)  from #category where Category in ( 2000 , 2200 )  and a.partid = partid group by partid having count(*) = 2 
)