Hierarchical query from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchical query from two tables

Author

Topic

rajeeva
Starting Member

1 Post

Posted - 2014-11-17 : 04:52:50

I have created a store procedure as below:
WITH TextType AS
(
SELECT AppTxtTypeId,AppTxtTypeCode, AppTxtTypeParentCode, Name,Description,Active,SortOrder ,0 as TypeLevel,AppTxtTypeId as parentId
FROM [ApplicationTextTypes]
WHERE AppTxtTypeParentCode IS NULL
),
SubL AS(
SELECT AppTxtTypeId=e.AppTxtTypeId,e.AppTxtTypeCode, e.AppTxtTypeParentCode,e.Name,e.Description,e.Active,e.SortOrder, m.TypeLevel+1 As Slevel ,m.AppTxtTypeId AS parentId
FROM [ApplicationTextTypes] e INNER JOIN TextType m
ON e.AppTxtTypeParentCode = m.AppTxtTypeCode

UNION ALL
SELECT AppTextId=e.AppTextId+8000,e.AppTxtCode, e.AppTxtTypeCode,e.Name,e.Description,e.Active,e.SortOrder,s.Slevel+1,s.AppTxtTypeId as parentId
FROM [ApplicationText] e INNER JOIN SubL s
ON e.AppTxtTypeCode = s.AppTxtTypeCode

)
select * from TextType
union all
select * from SubL
order by parentId

From this i am able to get data in the below format:

Parent
--Child1
--Child2
---Subchild1-Child1
---Subchild2-Child1
---Subchild1-Child2
---Subchild2-Child2

Actually my requirement is :
Parent
--Child1
---Subchild1-Child1
---Subchild2-Child1

--Child2
---Subchild1-Child2
---Subchild2-Child2

Can anyone suggest the changes in the above procedure or help me with the new query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 09:01:39

Add the child id to your ORDER BY:

ORDER BY parentID, childID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources