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

• • Reply
  ahmedbarbary

  Member

  38 Points

  350 Posts

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

  Dec 24, 2020 02:22 AM|ahmedbarbary|LINK

  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
  

• • Reply
  mgebhard

  All-Star

  52091 Points

  23222 Posts

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

  Dec 24, 2020 12:44 PM|mgebhard|LINK

  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.

• • Reply
  yij sun

  Contributor

  3360 Points

  1279 Posts

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

  Dec 25, 2020 05:39 AM|yij sun|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  ahmedbarbary

  Member

  38 Points

  350 Posts

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

  Dec 25, 2020 03:14 PM|ahmedbarbary|LINK

  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

• • Reply
  yij sun

  Contributor

  3360 Points

  1279 Posts

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

  Dec 29, 2020 09:16 AM|yij sun|LINK

  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

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.