0

I have a table with a list of pages, some with parent pages. These pages are in a very random order. I need to make a recursive join statement to select all pages ordered by Page_Order, where the parents are listed each followed by its child pages.

Table: Pages

Page_ID | Page_Name | Page_Parent | Page_Order
1       | User      | 2           | 2
2       | Admin     | NULL        | 2
3       | Pages     | 2           | 1
4       | Home      | NULL        | 1
5       | About     | NULL        | 3
6       | Contact   | 5           | 1

I want to select them in the following order (order the parent pages, each followed by its child pages):

Page_ID | Page_Name | Page_Parent | Page_Order
4       | Home      | NULL        | 1
2       | Admin     | NULL        | 2
3       | Pages     | 2           | 1
1       | User      | 2           | 2
5       | About     | NULL        | 3
6       | Contact   | 5           | 1
Ali Almohsen
  • 1,311
  • 3
  • 13
  • 24
  • 2
    Can you please share a little more about the logic behind the sorting? I'm not following how you're getting the desired order. – Siyual Mar 13 '17 at 13:21
  • What have you tried? What research have you done? – HABO Mar 13 '17 at 13:23
  • @Siyual I want the parents to be sorted by the column Page_Order. After each parent, should be its children again sorted by the column Page_Order. – Ali Almohsen Mar 13 '17 at 13:26
  • @HABO I looked at other posts like http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server and http://stackoverflow.com/questions/3471134/how-to-self-join-recursively-in-sql but they answered how to do it with a specific parent or didn't include any sorting. – Ali Almohsen Mar 13 '17 at 13:27
  • @AliAlmohsen Your description doesn't match the desired results you have - why are `User` and `Pages` with the parent of `2` switched? – Siyual Mar 13 '17 at 13:27
  • @Siyual Sorry, edited the mistake. – Ali Almohsen Mar 13 '17 at 13:28
  • If you want the parents ordered by `Page_Order`, shouldn't the "About" page be together with "Home" and "Admin", since they are all absolute parents and "About" has a `Page_Order` of 3? – 3BK Mar 13 '17 at 13:48
  • @3BK I want the child pages of parent to come immediately after the parent. – Ali Almohsen Mar 13 '17 at 13:54

1 Answers1

2

It's not specifically what you asked for but here is a way to achieve what you want. This does limit you too a finite number of child pages though and its not recursive.

; WITH cte AS (
  SELECT 1 Page_Id, 'User' Page_Name, 2 Page_Parent, 2 Page_Order union 
  SELECT 2, 'Admin   ', NULL, 2 union 
  SELECT 3, 'Pages   ', 2   , 1 union 
  SELECT 4, 'Home    ', NULL, 1 union 
  SELECT 5, 'About   ', NULL, 3 union 
  SELECT 6, 'Contact ', 5   , 1 
)

SELECT  Page_Id, Page_Name, Page_Parent, Page_order, x = CAST(Page_order  AS DECIMAL(18,2))
FROM    cte
WHERE   Page_Parent IS NULL
UNION
SELECT  child.Page_Id, child.Page_Name, child.Page_Parent, child.Page_order, X = CAST(parent.Page_order  AS DECIMAL(18,2)) + ISNULL(CAST(child.Page_order  AS DECIMAL(18,2))/10000, 0)
FROM    cte parent
        LEFT OUTER JOIN cte child 
                ON child.Page_Parent = parent.Page_Id
WHERE child.Page_Id IS NOT NULL
ORDER BY x
Steve
  • 710
  • 1
  • 6
  • 12