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

  • 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
    

    Thursday, December 24, 2020 2:22 AM

All replies

  • 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.

    Thursday, December 24, 2020 12:44 PM
  • 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

    Friday, December 25, 2020 5:39 AM
  • 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

    Friday, December 25, 2020 3:14 PM
  • 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

    Tuesday, December 29, 2020 9:16 AM