How to get feature not exist per parts

ahmed salah 3,216 Reputation points
2020-12-09T03:31:48.187+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,132 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,586 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-12-09T05:40:30.06+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,566 Reputation points
    2020-12-09T04:06:45.95+00:00

    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;
    
    0 comments No comments