How to get feature not exist per parts

Question

I work on SQL server 2012 I need to get parts not have features on table #feature meaning I need to get all parts
and if it not have feature then status will be NotExist and if Part have feature status will be Exist

create table #features
(
PartId  int,
ZFeaturekey  int

)
insert into #features (PartId,ZFeaturekey)
values
(12222,45555),
(12222,46788),
(54449,98342)

create table #categoryfeatures
(
ZFeaturekey  int

)

insert into #categoryfeatures(ZFeaturekey) values
(45555),
(46788),
(98342),
(8090),
(9090)

Result

PartId    zFeature  status
12222     98342     NotExist
12222    8090        NotExist
12222    9090        NotExist
54449     45555     NotExist
54449   46788       NotExist
54449    8090        NotExist
54449    9090        NotExist
12222    45555         Exist
12222    46788         Exist
54449    98342         Exist

so How to do that please

Answer 1

Hi @ahmed salah ,

Please also refer another method below:

  select c.*,   
  case when d.PartId is null then 'NotExist' else 'Exist' end Status from   
  (select distinct PartId,b.ZFeaturekey from #features a   
  cross apply  #categoryfeatures b) c  
  left join #features d   
  on c.PartId=d.PartId and c.ZFeaturekey=d.ZFeaturekey  

Output:

PartId	ZFeaturekey	Status  
12222	8090	NotExist  
12222	9090	NotExist  
12222	45555	Exist  
12222	46788	Exist  
12222	98342	NotExist  
54449	8090	NotExist  
54449	9090	NotExist  
54449	45555	NotExist  
54449	46788	NotExist  
54449	98342	Exist  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @features TABLE (PartId  int, ZFeaturekey  int);
INSERT INTO @features (PartId, ZFeaturekey) VALUES
(12222,45555),
(12222,46788),
(54449,98342);

 DECLARE @categoryfeatures TABLE (ZFeaturekey  int);
 INSERT INTO @categoryfeatures (ZFeaturekey) VALUES
 (45555),
 (46788),
 (98342),
 (8090),
 (9090);
 -- DDL and sample data population, end

;WITH rs AS
(
 SELECT DISTINCT PartId FROM @features
)
SELECT rs.PartId, cf.ZFeaturekey
 , IIF(f.PartId IS NULL, 'NotExist', 'Exist') AS [Status]
FROM rs
 CROSS JOIN @categoryfeatures AS cf
 LEFT JOIN @features AS f ON rs.PartId = f.PartId
 AND cf.ZFeaturekey = f.ZFeaturekey
ORDER BY rs.PartId, cf.ZFeaturekey;