How to get Parts that have two different value source type?

I work on sql server 2017 i need to get part id that have two different source type per same part .

but part have two different source type per part

and it must have one source type from two 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)

what i try

select partid from #temp
 where sourcetypeid=8901
 group by partid
 having count(distinct sourcetypeid)=2

but it return null

expected result

partid that have two different source type at least source type 8901 must exist

but it return null

The current query fails because the WHERE clause excludes all sourceTypeId except one: 8901.

PartId | SourceTypeId
-----: | -----------:
  1290 |         8901
  7001 |         8901
  9110 |         8901
  9110 |         8901

So when the HAVING clause searches for parts with two (2) SourceTypeId values, no matches are found, because the count is always 1:

PartId | COUNT(DISTINCT t.SourceTypeId)
-----: | -----------------------------:
  1290 |    1
  7001 |    1
  9110 |    1

Instead, try using EXISTS to ensure that one of two SourceTypeId's is 8901:

SELECT t.PartId
FROM   #temp t 
WHERE  EXISTS (
       SELECT 1
       FROM  #temp r
       WHERE r.SourceTypeId = 8901
       AND   r.partId = t.PartId
)
GROUP BY  t.PartId
HAVING COUNT(DISTINCT t.SourceTypeId)=2

Result:

| PartId |
| -----: |
|   1290 |