{"id":45,"date":"2006-04-10T11:43:37","date_gmt":"2006-04-10T16:43:37","guid":{"rendered":"http:\/\/stein.everybody.org\/brain\/sql-server-recursive-join-2\/"},"modified":"2011-03-10T10:57:33","modified_gmt":"2011-03-10T15:57:33","slug":"sql-server-recursive-join-2","status":"publish","type":"post","link":"https:\/\/jeremystein.com\/brain\/sql-server-recursive-join-2\/","title":{"rendered":"SQL Server recursive join"},"content":{"rendered":"<p>SQL Server 2000 does not support recursive joins.  Oracle&#8217;s PL\/SQL has the &#8220;connect by&#8221; mechanism, but T-SQL has nothing.  So, here&#8217;s a way to get all the IDs of the descendants into a table variable.  Assume a table MyTable has id and parent_id.  Modify to meet your needs:<\/p>\n<pre>\ndeclare @descendant table\n  (id int, depth int)\ndeclare @depth int\ndeclare @cnt int\n\nset @depth = 0\n\ninsert into @descendant\nselect id, @depth\nfrom MyTable\nwhere id = @initial_id --set this above\nset @cnt = @@rowcount\n\nwhile @cnt > 0\nbegin\n  set @depth = @depth + 1\n  insert into @descendant\n  select MyTable.id, @depth\n  from MyTable\n  inner join @descendant descendant\n    on descendant.id = MyTable.parent_id\n  where descendant.depth = @depth - 1\n  set @cnt = @@rowcount\nend\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2000 does not support recursive joins. Oracle&#8217;s PL\/SQL has the &#8220;connect by&#8221; mechanism, but T-SQL has nothing. So, here&#8217;s a way to get all the IDs of the descendants into a table variable. Assume a table MyTable has id and parent_id. Modify to meet your needs: declare @descendant table (id int, depth int) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15],"tags":[],"class_list":["post-45","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/posts\/45","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/comments?post=45"}],"version-history":[{"count":0,"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/posts\/45\/revisions"}],"wp:attachment":[{"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/media?parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/categories?post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jeremystein.com\/brain\/wp-json\/wp\/v2\/tags?post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}