mardi 7 juillet 2015

SQL- Left Join to Replace Not in


I'm trying to replace a NOT IN using a LEFT JOIN but for some reason couldn't get the desired result.
Here is the original query

create table #versions
(
    Ruleid int
   , IsDraft INT
)


-- prod #versions
insert into #versions values (1, 0)
insert into #versions values (2, 0) -- this will be deleted


-- Draft version
insert into #versions values (1, 1) -- changed added

ORIGINAL CODE (this should give '2'):

-- Getting all the rules that are deleted

    select distinct vp.ruleid from #versions vp where vp.IsDraft = 0
    except 
    select distinct vd.ruleid from #versions vd where vd.IsDraft = 1 

CHANGED CODE:

select vp.RuleId from #versions vp LEFT JOIN #versions vd ON vd.RuleId = vp.RuleId and vp.IsDraft = 0 and vd.IsDraft = 1

Am I missing something...???
Please advice.
Thanks.

Aucun commentaire:

Enregistrer un commentaire