CODEWITHSUNDEEP
sqloracle-database
Wind Waker
Wind Waker

Reputation: 13

Show childs after their parents in a self referenced table

Let's say I have a self-referenced table like this one

------------------------------
| ID | Name       | ParentID |
| 1  | Fruits     |          |
| 2  | Vegetables |          |
| 3  | Animals    |          |
| 4  | Whatever   |          |
| 6  | Orange     | 1        |
| 7  | Apple      | 1        |
| 8  | Banana     | 1        |
------------------------------

I need to do a query in order to retrieve the data as follows:

------------------------------
| ID | Name       | ParentID |
| 1  | Fruits     |          |
| 6  | Orange     | 1        |
| 7  | Apple      | 1        |
| 8  | Banana     | 1        |
| 2  | Vegetables |          |
| 3  | Animals    |          |
| 4  | Whatever   |          |
------------------------------

Showing the parent before the childs whenever it applies.

Upvotes: 0

Views: 46

Answers (2)

APH
APH

Reputation: 4154

If SQL server:

select * from MyTable
order by
case when ID in (select distinct parentID from MyTable) 
     then ID - 0.5 else isnull(parentID, 100000) end
, ID

Checks to see if an ID is a parent - if so, sorts it to just above the records that have it as a parent (by subtracting 0.5 from the ID).

Note: the isnull(parentID, 100000) is just an arbitrary large value to drop rows that neither are a parent nor have a parent to the bottom of the list - adjust as needed based on your data. You may want to replace 100,000 with a calculated value based on the largest ID in your table, if you have a lot of data and/or it changes frequently.

Upvotes: 0

amcdermott
amcdermott

Reputation: 1585

Assuming you are using some variant of SQL Server, you need to use a common table expression with some kind of path/breadcrumb column included...

;with cte as 
(
   select Id, Name, ParentID,
          convert(nvarchar(max), [Id]) as Breadcrumb
     from dbo.Location
    where ParentID is null
   --
   union all
   --
   select l.Id, l.Name, l.ParentID,
          cte.BreadCrumb + '\' + convert(nvarchar(max), l.[Id]) as Breadcrumb
     from dbo.Location l
    inner join cte on l.ParentId = cte.Id
)

select * 
  from cte
 order by Breadcrumb

Edit: You can use ID or Name as your breadcrumb value - depends how you want to sort your data really.

Upvotes: 2

Related Questions