ahmed elbarbary

ahmed elbarbary

  • NA
  • 1.6k
  • 254.1k

How to get parts that not have same features count on table settings b

Dec 24 2020 6:56 AM
How to get parts that not have same features count on table settings by code typeid and pl ?
 
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
 
  1. create table #settings  
  2.  (  
  3.  SettingId int,  
  4.  PLID  INT,  
  5.  CodeType int,  
  6.  Code nvarchar(50),  
  7.  featureKey int  
  8.  )  
  9.  insert into #settings   
  10.  values   
  11.  (1,2890,798541,'Ear99',NULL),  
  12.  (1,2890,798541,'Ear70',15000160),  
  13.  (1,2890,798541,'Ear99',15000171),  
  14.  (1,2890,798541,'e900841',15000160)  
  15.       
  16.  create table #partFeature  
  17.  (  
  18.  PartId int,  
  19.  FeatureKey int  
  20.  )  
  21.  insert into #partFeature   
  22.  values  
  23.  (9010,15000160),  
  24.  (7070,15000171) ,  
  25.  (9041,15000171) ,  
  26.  (2030,15000160) ,  
  27.  (5871,15000160) ,  
  28.  (5871,15000171) ,  
  29.  (7731,15000160) ,  
  30.  (7731,15000171)  
  31.   
  32. result I need  
  33.   
  34.  PartId    FeatureKey  
  35.  9010    15000160  
  36.  7070    15000171  
  37.  9041    15000171  
  38.  2030    15000160