sql server - Difference between two SELECTs -

i have table this:

create table navigation_trees (     id int not null primary key,     parent_id int null,     template_id not null ) 

because there foreign key whereby parent_id references id of same table, attempting delete row referenced parent_id of row violates referential integrity. sql server not allow on delete cascade because of (obvious) potential circular reference cascades.

so, i'm trying delete rows have template id other ( 158, 159 ). in order that, i'm trying select rows not target of reference (i.e. no other row's parent_id row's id), delete them, , repeat process in loop until there no more delete. each time batch deleted, had parent_id free reference rows on next pass.

initially doing temporary tables, seemed still run afoul of constraint. here query using 2 methods (with temp table, , direct on table itself) try identify rows delete:

-- method one: temp table filtering template ids select id, parent_id #navtrees navigation_trees template_id not in ( 158, 159 )  select distinct tnt1.id #set1 #navtrees tnt1 left outer join #navtrees tnt2 on tnt1.id = tnt2.parent_id tnt2.parent_id null  -- method two: filtering template ids directly in join select distinct tnt1.id #set2 navigation_trees tnt1 left outer join navigation_trees tnt2 on tnt1.id = tnt2.parent_id tnt2.parent_id null     , tnt1.template_id not in ( 158, 159 )  select count(*) #set1 -- produces: 106023  select count(*) #set2 -- produces: 102575  drop table #navtrees drop table #set1 drop table #set2 

the temp table method seems catching incorrect rows, , that's why fails referential integrity checking. why produce different numbers of rows?

after separating non-(158, 159) templates temporary table using first method, not looking @ (158, 159) templates more. but, figures suggest, of non-(158, 159) templates must parents of (158, 159) ones only. first method doesn't filter them out, while second 1 does.

you still use first method, need use original set right side of left join. or go second, single-query method.