How to get rows that have Null only on All rows on zkfeature based on same zplid and same codetypeid?

Transact-SQL
, ,
1

I work on SQL server 2012 . I face issue I can't get rows from table #gen when it have Null only on all rows on

zfeaturekey field based on zplid and codetypeid .

meaning I need to get rows that have NULL only on all rows 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,NULL),
  (849774,25820,NULL),
  (849774,25820,NULL),
  (849774,25820,NULL),
  (987431,26777,NULL),
  (987431,26777,1502280005),
  (987431,26777,1502290001)
 CodeTypeId    Zplid    Zfeaturekey
 854838            25820    NULL
 849774            25820    NULL
 849774            25820    NULL
 849774            25820    NULL
 849774            25820    NULL

Expcted Result :

 CodeTypeId    Zplid  COUNT
 854838          25820        1
 849774          25820        4

I not take zplid 26777 because it have Null and values as 1502280005 on another rows

so I need to select where zfeaturekey is NULL on all rows by zplid and codetypeid

2

What have you tried so far?

3 
SELECT * FROM #gen g
WHERE EXISTS(SELECT 1 FROM #gen a
WHERE g.CodeTypeId=a.CodeTypeId AND g.Zplid=a.Zplid
GROUP BY a.CodeTypeId, a.Zplid HAVING MAX(a.Zfeaturekey) IS NULL)

I think there's a shorter way to do the same thing but can't think of it now, and I'm not sure it works on SQL Server 2012.

4

thank you