SQLTeam.com | Weblogs | Forums

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

Transact-SQL
,
#1

How to get parts that not have same features count on table settings by code typeid and pl ?

I work on sql server 2012 . I can't get parts that not have all features on table settings

so I need to display parts that not have all features exist on table settings

by codetypeand and plid and 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
#2 
SELECT pf.PartId, pf.FeatureKey
FROM #partFeature pf
INNER JOIN (
    SELECT pf.PartId
    FROM #partFeature pf
    CROSS JOIN (
        SELECT COUNT(DISTINCT featureKey) AS featureKeyCount
        FROM #settings
    ) AS s
    GROUP BY PartId
    HAVING COUNT(pf.FeatureKey) < MAX(s.featureKeyCount)
) AS pf2 ON pf2.PartId = pf.PartId
ORDER BY PartId, FeatureKey