How to get parts that not have all features Exist on table settings by codetype and plid ?

Question

User696604810 posted

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

User475983607 posted

Do a basic GROUP BY HAVING a COUNT equal to one WHERE the feature id is 15000171 OR 15000160.   This will give you the IDs.  Then just do a JOIN.  

https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-transact-sql?view=sql-server-ver15

At least read the doc and give the code a try.

Answer 2

User1535942433 posted

Hi ahmedbarbary,

Accroding to your description,as far as I think,you need to group by PartId. And if count PartId =1,you could show the #partFeature table data.

Just like this:

select PartId,FeatureKey from #partFeature  where PartId in (select PartId from #partFeature group by PartId  having COUNT(PartId)=1 )

Best regards,

Yijing Sun

Answer 3

User696604810 posted

thank you for reply

i don't need partid have count =1 I need to get parts that not have full features 

on my case i have zplid and code type have 2 features or 3 features  on table settings

so I need to get parts from table parts feature that not have 2 or 3 features depend on table settings

so on my case i will get parts above correct

but this because i have two features on table settings for zplid and code type

and these part i need to display have only one feature so 

Exactly i need to get parts that have count of feature for part less than features on table settings

so these parts above have one feature and i have two features on table settins

so how to do that by sql query

Answer 4

User1535942433 posted

Hi ahmedbarbary,

Accroding to your description,I don't understand your requirment clearly.I doubt that I miss something you don't post to us.

I'm guessing that you have more than one features of one code type. And you need to count the num of features in table setting.When you get the result,you need to join table setting and table partFeature. If full features of same PartId in table partFeature, it will not be out put.

Just like this:

       part
         |    
       code    
      /    \          
feature      feature

Best regards,

Yijing Sun