CODEWITHSUNDEEP
sqlsql-server-2008hierarchical-data
user441222
user441222

Reputation: 2001

SQL SERVER 2008 tree query

I have a table called Module. The table structure some things like:

ModuleID     ModuleName        ParentID

   1            System Manage       0
   2            Database Manage     1
   3            Area Manage         1
   4            Basic Setting       0

I would like to get the results below by a sql statement.

   ModuleID       ModuleName        ParentMoudle
     1               System Manage      0
     2               Database Manage    System Manage
     3               Area Manage        System Manage 
     4               Basic Setting      0

I am a sql newbie .Thank you very much!

Upvotes: 2

Views: 1198

Answers (2)

Yaroslav
Yaroslav

Reputation: 6534

This should work:

;WITH Tree AS
(
    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName
      FROM @TT
     WHERE ParentID IS NULL
    UNION ALL
    SELECT Fam.*,Tree.ModuleName AS ParentName
      FROM @TT AS Fam
     INNER JOIN Tree
        ON Fam.ParentID = Tree.moduleID
)
SELECT * FROM Tree

Upvotes: 0

indiPy
indiPy

Reputation: 8062

Parent child relationship can be achived by LEFT OUTER JOIN and querying same table.

      select m.moduleid,m.modulename,COALESCE(p.modulename,0) from table as m
      left outer join table as p on m.moduleid = p.module.id

Upvotes: 1

Related Questions