Menu
Let's say I have the following table:
CustomerID ParentID Name ========== ======== ==== 1 null John 2 1 James 3 2 Jenna 4 3 Jennifer 5 3 Peter 6 5 Alice 7 5 Steve 8 1 Larry I want to retrieve in one query all the descendants of James (Jenna,Jennifer,Peter, Alice, Steve). Thanks, Pablo.
610
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later. Here is a sample. Query (SELECT 1 AS n) now have a name — R .
First, specify the name of the view that you want to create in the CREATE RECURSIVE VIEW clause. You can add an optional schema-qualified to the name of the view. Second, add the SELECT statement to query data from base tables. The SELECT statement references the view_name to make the view recursive.
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion. The iterative fullselect contains a direct reference to itself in the FROM clause.
On SQL Server 2005 you can use CTEs (Common Table Expressions) :
with Hierachy(CustomerID, ParentID, Name, Level) as ( select CustomerID, ParentID, Name, 0 as Level from Customers c where c.CustomerID = 2 -- insert parameter here union all select c.CustomerID, c.ParentID, c.Name, ch.Level + 1 from Customers c inner join Hierachy ch on c.ParentId = ch.CustomerID ) select CustomerID, ParentID, Name from Hierachy where Level > 0 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
