Find lowest parent in recursive SQL table

Question

Post reply

Find lowest parent in recursive SQL table

vijay.s

SSCrazy

Points: 2161
More actions
January 5, 2012 at 3:16 am

#249138

I have a table named as Modules
CREATE TABLE #Modules
(
[ModuleId] [int] ,
[ModuleName] [varchar](50) NOT NULL,
[ParentId] [int] NULL
Insert into #Modules (1, 'Master', null)
Insert into #Modules (2, 'List', 1)
Insert into #Modules (3, 'EmployeeList', 2)
Insert into #Modules (4, 'UsersList', 2)
Insert into #Modules (5, 'Groups', 1)
Insert into #Modules (6, 'EmployeeGroup', 5)
Insert into #Modules (7, 'UsersGroup', 5)
)
Now what I want is select module name with lowest parent name
like
"Master->EmployeeList"
"Master->UsersList"
Master->'EmployeeGroup'
Master->'UsersGroup'
I need above mentioned output

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

There are example recursive CTE's all over the web.

A near identical example is on the Microsoft website. This smells like homework

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)

INSERT INTO #Modules

SELECT 1, 'Master', NULL

UNION ALL SELECT 2, 'List', 1

UNION ALL SELECT 3, 'EmployeeList', 2

UNION ALL SELECT 4, 'UsersList', 2

UNION ALL SELECT 5, 'Groups', 1

UNION ALL SELECT 6, 'EmployeeGroup', 5

UNION ALL SELECT 7, 'UsersGroup', 5;

WITH CTE AS (SELECT 1 AS [Level], [ModuleId],

CONVERT(VARCHAR(MAX), [ModuleName]) AS [ModuleName], [ParentId]

FROM #Modules

WHERE [ParentId] IS NULL

UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],

cycle.[ModuleName] + '-->' + base.[ModuleName], base.[ParentId]

FROM #Modules base

INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])

SELECT [ModuleId], [ModuleName]

FROM CTE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vijay.s SSCrazy Points: 2161 More actions · Answer 3

I tried from my side but it will give me following output

WITH

cteMenu AS

(

SELECT ModuleId, ParentId, Caption, Level = 1, HierarchicalPath = CAST(CAST(Caption AS VARCHAR(50)) AS VARCHAR(4000))

FROM dbo.modules

WHERE parentid IS NULL

UNION ALL

SELECT e.ModuleId, e.ParentId, e.Caption,Level = d.Level + 1,

HierarchicalPath = CAST(d.HierarchicalPath + '>>'.Caption AS VARCHAR(50)) AS VARCHAR(4000))

FROM dbo.sys_modules e

INNER JOIN cteMenu d ON e.ParentId = d.moduleid

)

SELECT ModuleId,

ParentId,

Name = SPACE((Level-1)*4) + Caption,

Level,

HierarchicalPath

FROM cteMenu

ORDER BY HierarchicalPath

I will give Me out put like 'Parent>>Child>>Grand Child'

i need 'Parent>>Grand Child'

Vijay

vijay.s SSCrazy Points: 2161 More actions · Answer 4

@Cadavre

Your query is also returning the same result as mentioned above.

I just want to lowest parent (nth parent) and highest child.

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

vijay.s (1/5/2012)
@Cadavre
Your query is also returning the same result as mentioned above.
I just want to lowest parent (nth parent) and highest child.

Oh, misread the requirements.

CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)

INSERT INTO #Modules

SELECT 1, 'Master', NULL

UNION ALL SELECT 2, 'List', 1

UNION ALL SELECT 3, 'EmployeeList', 2

UNION ALL SELECT 4, 'UsersList', 2

UNION ALL SELECT 5, 'Groups', 1

UNION ALL SELECT 6, 'EmployeeGroup', 5

UNION ALL SELECT 7, 'UsersGroup', 5;

WITH CTE AS (SELECT 1 AS [Level], [ModuleId],

[ModuleName] AS [GrandParent], [ModuleName], [ParentId]

FROM #Modules

WHERE [ParentId] IS NULL

UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],

cycle.[GrandParent], base.[ModuleName], base.[ParentId]

FROM #Modules base

INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])

SELECT [ModuleId], COALESCE(NULLIF([GrandParent],[ModuleName])+'-->'+[ModuleName],[ModuleName]) AS [ModuleName]

FROM CTE

--EDIT--

That might not be what you're after either. Do you mean that you only want to display the root and any leaf that has no children?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vijay.s SSCrazy Points: 2161 More actions · Answer 6

@Cadavre

Thankx, I believe that will solve my problem.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

If you did mean that all you want is the "root" and the last leaf, try this instead: -

BEGIN TRAN

CREATE TABLE #Modules ([ModuleId] INT, [ModuleName] VARCHAR(50) NOT NULL, [ParentId] INT NULL)

INSERT INTO #Modules

SELECT 1, 'Master', NULL

UNION ALL SELECT 2, 'List', 1

UNION ALL SELECT 3, 'EmployeeList', 2

UNION ALL SELECT 4, 'UsersList', 2

UNION ALL SELECT 5, 'Groups', 1

UNION ALL SELECT 6, 'EmployeeGroup', 5

UNION ALL SELECT 7, 'UsersGroup', 5;

WITH CTE AS (SELECT 1 AS [Level], [ModuleId],

[ModuleName] AS [GrandParent], [ModuleName], [ParentId]

FROM #Modules

WHERE [ParentId] IS NULL

UNION ALL SELECT cycle.[Level] + 1, base.[ModuleId],

cycle.[GrandParent], base.[ModuleName], base.[ParentId]

FROM #Modules base

INNER JOIN CTE cycle ON cycle.[ModuleId] = base.[ParentId])

SELECT nodes.[ModuleId],

COALESCE(NULLIF(nodes.[GrandParent],nodes.[ModuleName])+'-->'+nodes.[ModuleName],nodes.[ModuleName]) AS [ModuleName]

FROM CTE nodes

--Get child ID

OUTER APPLY (SELECT [ParentId], [ModuleId] AS childid

FROM CTE child

WHERE nodes.[ModuleId] = child.[ParentId]) children

WHERE childid IS NULL

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vijay.s SSCrazy Points: 2161 More actions · Answer 8

Yeh..

You are right other wise where ParentId is null condition is enough for me as i have logic in my front end that call only child node. I need this query only show the concatenate caption to user.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

CELKO (1/5/2012)
...Buy a copy of HIKING IN SETS for more details...

My cat bought this book and got completely lost on the first example - with a catnav.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Sean Lange SSC Guru Points: 286602 More actions · Answer 10

CELKO
Buy a copy of HIKING IN SETS for more details.

Right after I finish THINKING IN THE WOODS.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Find lowest parent in recursive SQL table

Cookies on SQLServerCentral