Post reply

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

  • December 19, 2020 at 8:44 pm

    #3822435

    Exactly I need zplid and codetypeid that have feature value

    on all rows .

    if I have Null on zfeature key on one row then I don't Need it .

    I need to get zplid that have features Not Null on all rows as zplid 9714

    but 9823,5890 have Null so 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)

    I tried to do as below :

    select zplid,CodetypId,ZfeatureKeyfrom #gen

    where ZfeatureKey is not null

    but it give me zplid and codetypeid that have values ON Zfeature key on all rows of all zplid

    and this I don't need

  • December 19, 2020 at 10:00 pm

    #3822444

    You mean something like this?

     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);

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

    select zplid,CodetypId,ZfeatureKey
    from #gen as g
    where not exists(select 1 from #gen g1 where g1.ZfeatureKey is null and g1.zplid = g.zplid and g1.CodetypId = g.CodetypId);

    drop table #gen;

  • January 11, 2021 at 3:42 am

    This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply