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