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

Question

User696604810 posted

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

User-939850651 posted

Hi ahmedbarbary,

You could try a query like this:

select c.PartId,Category from #category c
join (
	select  PartId, count(PartId) rowss
	from  #category
	where  Category in (2000,2200)
	group by  PartId
	having count(PartId) = 2
     ) temp 
on temp.PartId = c.PartId 
order by PartId

Hope this can help.

Best regards,

Xudong Peng