How to get zplid that have zfeature key value on all rows?

Transact-SQL
,
1

How to get zplid that have zfeature key value onall rows ?

Exactly I need zplid and codetypeid that have zfeaturekey value
on all rows
if I have Null on zfeature key on one row then I don't Need it .

this is my sample data

create table #gen
(
zplid int,
CodetypId int,
ZfeatureKey nvarchar(20)
)
insert into #gen(zplid,CodetypId,ZfeatureKey)
values
(9714,849774,1501290046),
(9714,849774,1501099991),
(9714,849774,1501555555),
(9823,9732410,1501290046),
(9823,9732410,1501099991),
(9823,9732410,NULL),
(5890,838651,1501290046),
(5890,838651,NULL),
(5890,838651,NULL)

expected result is

zplid CodetypId ZfeatureKey
9714 849774 1501290046
9714 849774 1501099991
9714 849774 1501555555

I tried to do as below :slight_smile:

 select zplid,CodetypId,ZfeatureKey 
 from #gen 
 where ZfeatureKey is not null

but it give me all rows that have values on all rows
and this i don't need

2 
SELECT * FROM #gen a
WHERE NOT EXISTS(SELECT 1 FROM #gen b WHERE a.zplid=b.zplid AND b.zfeaturekey IS NULL)