How to get rows that have Null and values on zkfeature both based on same zplid and same codetypeid ?

ahmed salah 3,216 Reputation points
2020-12-08T20:08:27.43+00:00

I work on sql server 2012 . I face issue I can't get rows from table #gen when it have Null and values on

zfeaturekey based on zplid and codetypeid .

meaning I need to get rows that have NULL ON zfeaturekey and Values on Zfeaturekey but must be same code

typeid and same zplid .

create table #gen
        (
        CodeTypeId int,
        Zplid  int,
        Zfeaturekey nvarchar(50)
        )
        insert into #gen values
(854838,25820,NULL),
(849774,25820,1502260001),
(849774,25820,1502260001),
(849774,25820,1502260016),
(849774,25820,NULL),
(987431,26777,1502270003),
(987431,26777,1502280005),
(987431,26777,1502290001)

Expcted Result :

CodeTypeId  Zplid   Zfeaturekey
849774  25820   1502260001
849774  25820   1502260001
849774  25820   1502260016
849774  25820   NULL

i will not get codetypeid 854838 and zplid 25820 because it have NULL Only on zfeaturekey
i will not get codetypeid 987431 and zplid 26777 because it Not have NULL ON zfeaturekey

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

Accepted answer
  1. ahmed salah 3,216 Reputation points
    2020-12-08T20:56:06.197+00:00

    yes this is correct but if i need to get it as summarize like that

    CodeTypeId    Zplid    countfeaturekey
     849774         25820          4
    

    so How i do that as above summarize result


1 additional answer

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2020-12-08T20:33:34.43+00:00

    Check a direct approach:

    select * from #gen
    where CodeTypeId in ( select CodeTypeId from #gen group by CodeTypeId having count(distinct Zplid) = 1 )
    and CodeTypeId in ( select CodeTypeId from #gen where Zfeaturekey is null)
    and CodeTypeId in ( select CodeTypeId from #gen where Zfeaturekey is not null)
    
    0 comments No comments