sql server 2008 - Common table expression from last child record -
hi have sitemap table , hierarchy table.
use [eb_new] go /****** object: table [dbo].[common.sitemap] script date: 07/24/2013 06:13:51 ******/ set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[common.sitemap]( [id] [int] identity(1,1) not null, [p_id] [int] null, [c_id] [int] null, [type] [varchar](50) null, [title] [varchar](50) null, [task_url_id] [bigint] null ) on [primary] go set ansi_padding off go set identity_insert [dbo].[common.sitemap] on insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (2, null, 2, n'eb', n'employee benefit', null) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (3, 2, 3, n'company', n'company listing', 175) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (4, 3, 4, n'company', n'company profile setup', 176) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (5, 4, 5, n'company', n'company policy setup', 191) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (6, 4, 6, n'company', n'employee profile setup', 178) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (7, 5, 7, n'company', n'company policy setup 2', null) insert [dbo].[common.sitemap] ([id], [p_id], [c_id], [type], [title], [task_url_id]) values (8, 6, 8, n'company', n'employee profile setup 2', null) set identity_insert [dbo].[common.sitemap] off result
id p_id c_id type title task_url_id 2 null 2 eb employee benefit null 3 2 3 company company listing 175 4 3 4 company company profile setup 176 5 4 5 company company policy setup 191 6 4 6 company employee profile setup 178 7 5 7 company company policy setup 2 null 8 6 8 company employee profile setup 2 null i used common table expression determined hierarchy data
with ctlevel ( select c_id child ,p_id parent ,1 [level] ,task_url_id taskurl_id ,title title ,type type ,id id [common.sitemap] common_sitemap p_id null , common_sitemap.type ='eb' union select c_id child ,p_id parent ,[level] + 1 [level] ,task_url_id ctaskurl_id ,common_sitemap.title ctitle ,common_sitemap.type ctype ,common_sitemap.id cid [common.sitemap]as common_sitemap inner join ctlevel on ( p_id = child ) common_sitemap.p_id not null ) select distinct [parent] ,[child], ctlevel.title,ctlevel.type, ctlevel.taskurl_id, ctlevel.level, common_task_url.task_url ctlevel left join [common.task_url]as common_task_url on ctlevel.taskurl_id = common_task_url.task_url_id order level result
parent child title type taskurl_id level task_url ----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------- ----------- ------------------- null 2 employee benefit eb null 1 null 2 3 company listing company 175 2 ~/module/eb/company/companylist.aspx 3 4 company profile setup company 176 3 ~/module/eb/company/companydetail.aspx 4 5 company policy setup company 191 4 ~/module/eb/company/companypolicy.aspx 4 6 employee profile setup company 178 4 ~/module/eb/employee/employeedetail.aspx 5 7 company policy setup 2 company null 5 null 6 8 employee profile setup 2 company so question , possible entire related hierarchy record last child record.for example child record 7
so result get
parent child title type taskurl_id level task_url ----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------- ----------- ----------------------- null 2 employee benefit eb null 1 null 2 3 company listing company 175 2 ~/module/eb/company/companylist.aspx 3 4 company profile setup company 176 3 ~/module/eb/company/companydetail.aspx 4 5 company policy setup company 191 4 ~/module/eb/company/companypolicy.aspx 5 7 company policy setup 2 company null 5 null but if child record 8
parent child title type taskurl_id level task_url ----------- ----------- -------------------------------------------------- -------------------------------------------------- -------------------- ----------- ----------------------- null 2 employee benefit eb null 1 null 2 3 company listing company 175 2 ~/module/eb/company/companylist.aspx 3 4 company profile setup company 176 3 ~/module/eb/company/companydetail.aspx 4 6 employee profile setup company 178 4 ~/module/eb/employee/employeedetail.aspx 6 8 employee profile setup 2 company null 5 null any souliton? thanks
it possible. it's similar how traverse down tree. i've made example fiddle: http://sqlfiddle.com/#!3/842ce/13
declare @childid int = 7; tree ( select parent, id t parent null union select t.parent, t.id t join tree on t.parent = tree.id ), bottomup ( select parent, id tree id = @childid union select t.parent, t.id tree t join bottomup on bottomup.parent = t.id ) select * bottomup order id asc here traversing tree traversed down. however, don't have use first tree if don't want to. change tree t in bottomup, , still works. if have logic trimming tree going down, can keep it. if don't have logic, it's faster if have second cte without first.
Comments
Post a Comment