-1

I work on sql server 2017 i face issue i can't get partnumber

that have at least source type website and at least have stockid equal 1 per partnumber

so with another meaning i need to get part numers that have stockid 1 and source type website

so i need to make select query get part numbers that must have source type website and stock =1 Based on Part number

sample data

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),

(8541,'Website',0)

expected result

expected result have website and stock id 1

1 Answer 1

1

One way would be using exists:

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

Demo

EDIT To get distinct partnumber only use:

select distinct PartNumber
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
           );

Demo

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.