Asked by:
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
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