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

ahmed elbarbary

5y913

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
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
);
0
Pankajkumar Patel5y ago
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!
+1
Junaid Musla5y ago
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 !
0