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

Pankaj Patel · Answer

Hi Ahmed Elbarbary, Using Common Table Expression (CTE) with INTERSECT and INNER JOIN you can " get partid that have two category and exist on 2000,2200 " as following: ; with CTE as ( select PartId from category where category = 2000 intersect select PartId as Category from category where category = 2200) select cat.PartId, cat.Category FROM CTE inner join category cat on cat.PartId = CTE.PartId For more detail you can refer below article: The Complete Reference - Set Operations In MS SQL - UNION [ALL], INTERSECT, EXCEPT https://www.c-sharpcorner.com/article/the-complete-reference-set-operations-in-ms-sql-union-all-intersect-excep/ Hope, this will help you!

Rajanikant Hawaldar · Answer

Try below 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 );

Junaid Musla · Answer

I tried to find the solution and below is the query for the same. select * from Tempcategory where PartId in ( select partid from Tempcategory where category in (2000,2200) group by partid having count (partid)=2) I hope this query will be fine for you !