I work on sql server 2017 i need to get different part id that have two different source type
per same part
but it must part have two different source type
and it must one source type from two have source type equal 8901 .
sample data
create table #temp
(
partid int,
sourcetypeid int
)
insert into #temp(partid,sourcetypeid)
values
(1290,5012),
(1290,5012),
(1290,8901),
(3501,5402),
(3501,74430),
(7001,8901),
(7321,8900),
(2040,5090),
(2040,5400),
(7321,7400),
(9110,8901),
(9110,8901)
expected result as below
partid sourcetypeid
1290 5012
1290 5012
1290 8901
i get part id 1290 because i need to get part that have two distinct source type
as 5012 and 8901 .
so i need parts that have two distinct source type
and mandatory must be source type 8901 one from these two source type
and i don't need this partid 2040 because it have two source type different but
source type 8901 not one from these two source type
What I have tried:
what i try but it return null
select partid from #temp
where sourcetypeid=8901
group by partid
having count(distinct sourcetypeid)=2