linked list using CTE

[Ali Al Omairi Abu AlHassan]

guys; I trying to design a query that returns products ordered based on a self-reference foreign key (just like a linked list). I tried to make a recursive query (CTE), but i couldn't return the records as the table below.

product_id | next_id | product_name

5 | 3 | product 5
3 | 2 | product 3
2 | 4 | product 2
4 | 1 | product 4
1 | null | product 1

I am wondering if you can help me with this.

Help people,so poeple can help you.

[Ali Al Omairi Abu AlHassan]

finally, i designed a query that returns the desired results. its the following one.

create table products(product_id int, next_id int, product_name nvarchar(50));
insert into products(product_id, next_id, product_name)
Values (1, null, N'product1')
,(2, 4, N'product2')
,(3, 2, N'product3')
,(4, 1, N'product4')
,(5, 3, N'product5');

WITH LinkedList (product_id, next_id, product_name, Level)
AS
(
-- Anchor member definition
SELECT e.product_id, e.next_id, e.product_name,
0 AS Level
FROM products AS e
WHERE e.next_id IS NULL
UNION ALL
-- Recursive member definition
SELECT e.product_id, e.next_id, e.product_name,
Level + 1
FROM products AS e
INNER JOIN LinkedList AS d
ON e.next_id = d.product_id
)
-- Statement that executes the CTE
SELECT product_id, next_id, product_name
FROM LinkedList
Order by Level desc;

drop Table products;

Help people,so poeple can help you.