By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #stockyes
(
PartNumber varchar(100),
sourcetype varchar(100),
stockid tinyint
)
insert into #stockyes(PartNumber,sourcetype,stockid)
values
(1233,'Website',1),
(1233,'Website',0),
(1233,'Datasheet',1),
(8901,'Website',1),
(8901,'Website',0),
(9015,'Website',1),
(7801,'Website',1),
(7801,'Website',1),
(9401,'Datasheet',0),
(5688,'Datasheet',0),
(5688,'Datasheet',0),
(5688,'Lifecycle',2),
(5688,'Lifecycle',2),
(8409,'Lifecycle',1),
(6703,'Website',0),
(6703,'Website',0),
(6703,'Website',0),
(3099,'Website',0),
(3099,'Website',0),
20 rows affected
SELECT * FROM #stockyes;
| PartNumber | sourcetype | stockid |
|---|---|---|
| 1233 | Website | 1 |
| 1233 | Website | 0 |
| 1233 | Datasheet | 1 |
| 8901 | Website | 1 |
| 8901 | Website | 0 |
| 9015 | Website | 1 |
| 7801 | Website | 1 |
| 7801 | Website | 1 |
| 9401 | Datasheet | 0 |
| 5688 | Datasheet | 0 |
| 5688 | Datasheet | 0 |
| 5688 | Lifecycle | 2 |
| 5688 | Lifecycle | 2 |
| 8409 | Lifecycle | 1 |
| 6703 | Website | 0 |
| 6703 | Website | 0 |
| 6703 | Website | 0 |
| 3099 | Website | 0 |
| 3099 | Website | 0 |
| 8541 | Website | 0 |
select PartNumber,sourcetype,stockid
from #stockyes s
where exists (select null from #stockyes s1 where s.PartNumber=s1.PartNumber and s1.sourcetype ='Website')
and exists (select null from #stockyes s2 where s.PartNumber=s2.PartNumber and s2.stockid =1);
| PartNumber | sourcetype | stockid |
|---|---|---|
| 1233 | Website | 1 |
| 1233 | Website | 0 |
| 1233 | Datasheet | 1 |
| 8901 | Website | 1 |
| 8901 | Website | 0 |
| 9015 | Website | 1 |
| 7801 | Website | 1 |
| 7801 | Website | 1 |