How to get parts that not have same features count on table settings by code typeid and pl ?

Question

I work on sql server 2012 . if face issue I can't get parts that not have all features on table settings

so I will count distinct features from table settings and compare it with count features per part

if count features per part on table part feature <>count distinct features on table settings by PLID and code type

then display it .

as example part 9010 have only one feature 15000160 from table settings and not have feature 15000171
then display it .
as example part 9041 have only one feature 15000171 from table settings and not have feature 15000160
then display it .
part id 7731 have two features 15000171,15000160 as part settings have two features then no need
to display it because it have full features

so How to write query do that

create table #settings
(
SettingId int,
PLID  INT,
CodeType int,
Code nvarchar(50),
featureKey int
)
insert into #settings 
values 
(1,2890,798541,'Ear99',NULL),
(1,2890,798541,'Ear70',15000160),
(1,2890,798541,'Ear99',15000171),
(1,2890,798541,'e900841',15000160)

create table #partFeature
(
PartId int,
FeatureKey int
)
insert into #partFeature 
values
(9010,15000160),
(7070,15000171) ,
(9041,15000171) ,
(2030,15000160) ,
(5871,15000160) ,
(5871,15000171) ,
(7731,15000160) ,
(7731,15000171)

result I need

PartId  FeatureKey
9010    15000160
7070    15000171
9041    15000171
2030    15000160

Answer 1

Maybe also check this approach:

select *
from #partFeature
where PartId in
(
    select PartId from
    (
        select f.PartId, s.featureKey
        from #partFeature f, #settings s
        where s.featureKey is not null
        except 
        select *
        from #partFeature
    ) d
)

However, it does not use the unclear PLID and CodeType columns.

Answer 2

Hi @ahmed salah ,

Please refer to:

;with cte  
as(select *,row_number()over(partition by PLID,CodeType,FeatureKey order by PLID )rn from #settings  
where FeatureKey is not null)  
,cte2 as(select *,count(rn)over(partition by PLID,CodeType order by PLID ) countnums from cte  
where rn<2)  
,cte3 as(select PartId,FeatureKey,count(FeatureKey)over(partition by PartId order by PartId) countnump   
from #partFeature)  
  
select PartId,FeatureKey from cte3  
where countnump<>(select max(countnums) from cte2)  

Output:

PartId,FeatureKey  
9041 15000171  
9010 15000160  
7070 15000171  
2030 15000160  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

thank you for reply

there are something need modify on statment below

i need to count of table settings <> count on table partfeature

why <2

i need if count different between two table then display parts

cte2 as(select *,count(rn)over(partition by PLID,CodeType order by PLID ) countnums from cte
 where rn<2)