StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is the link I used to find a solution..</p> <p><a href="http://wiki.lessthandot.com/index.php/Using_Common_Table_Expressions_for_Parent-Child_Relationships" rel="nofollow">http://wiki.lessthandot.com/index.php/Using_Common_Table_Expressions_for_Parent-Child_Relationships</a></p> <p>EDIT - @Pshimo - thanks. Heres the guide form the link.</p> <p>With SQL 2005 though it is a dream. Say you have this sample data:</p> <pre><code>declare @test table (bunchof uniqueidentifier default newid(), columns uniqueidentifier default newid(), Id int, ParentID int) insert @test (Id, ParentId) select 1, null union all select 5, 1 union all select 15, 2 union all select 16, 5 union all select 27, 16 </code></pre> <p>And you want to get all child rows for 1 (so ItemId 5, 16, 27)</p> <pre><code> declare @parentId int set @parentId = 1 ;--last statement MUST be semicolon-terminated to use a CTE with CTE (bunchof, columns, Id, ParentId) as ( select bunchof, columns, Id, ParentId from @test where ParentId = @parentId union all select a.bunchof, a.columns, a.Id, a.ParentId from @test as a inner join CTE as b on a.ParentId = b.Id ) select * from CTE </code></pre> <p>and if you want to include the parent:</p> <pre><code>declare @Id int set @Id = 1 ;--last statement MUST be semicolon-terminated to use a CTE with CTE (bunchof, columns, Id, ParentId) as ( select bunchof, columns, Id, ParentId from @test where Id = @Id union all select a.bunchof, a.columns, a.Id, a.ParentId from @test as a inner join CTE as b on a.ParentId = b.Id ) select * from CTE </code></pre> <p>You can select depth in the hierarchy as well, if you're into that kind of thing:</p> <pre><code>declare @Id int set @Id = 1 ;--last statement MUST be semicolon-terminated to use a CTE with CTE (bunchof, columns, Id, ParentId, Depth) as ( select bunchof, columns, Id, ParentId, 0 from @test where Id = @Id union all select a.bunchof, a.columns, a.Id, a.ParentId, b.Depth + 1 from @test as a inner join CTE as b on a.ParentId = b.Id ) select * from CTE </code></pre> <p>As you can see what you're doing here is first selecting your initial recordset, which contains all child rows for your parent ID parameter. You can then union to another query that joins to the CTE itself, to get the children's children (and their grandchildren, and so forth until you reach the last descendant row. Its' important to note that the default recursion limit is 100, so pay attention to the depth of your hierarchy when using these. You can change the recursion limit using the OPTION (MAXRECURSION)</p> <pre><code> WITH CTE AS ( ... ) SELECT * FROM CTE OPTION (MAXRECURSION 1000) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload