Solved

Parent-Child Traversing - getting all children & sub-children for parent?

Posted on 2008-10-28
6
1,374 Views
Last Modified: 2012-05-05
I'm trying to retrieve all children and sub-children for a given parent

Thre are two columns in the table I am referencing (that we need):
Category_ParentID and Category_ChildID

here is an example of some (BS) data:
ParentID | ChildID
1 | 4
4 | 7
7 | 15
7 | 26
15 | 312

I want to provide ID 1 and have the following returned to me
4
7
15
26
312

My SQL statement was lifted from another forum and modified to suit my needs, but I am receiving the following error:
Incorrect syntax near the keyword 'with'.

Does MS SQL 2000 use an older version of T-SQL or something?

Any help would be greatly appreciated - points are maxed out on this one in hopes of a quick & easy answer
set statistics time on
set statistics io on
declare @Topid_in int -- The top level we want to resolve children for
select 
    @Topid_in = 2953; 
with 
    subchildren (Category_ChildID, Category_ParentID) AS	
    (
        select 
            Category_ChildID, 
            Category_ParentID 
        from 
            Category_Relationship
        where 
            Category_ChildID = @Topid_in 
 
        union all	
 
        select 
            Category_Relationship.Category_ChildID, 
            Category_Relationship.Category_ParentID
        from 
            Category_Relationship	
            inner join 
                subchildren	
                on Category_Relationship.Category_ParentID = subchildren.Category_ParentID
    )
select * from subchildren

Open in new window

0
Comment
Question by:trickyidiot
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22827112
It looks like you want to change the "with" word to "Insert Into"
Since you are loading that table with the inner query results.

Insert into subchildren(Category_ChildID, Category_ParentID)
    (
        select
            Category_ChildID,
            Category_ParentID
        from
            Category_Relationship
        where
            Category_ChildID = @Topid_in
 
        union all      
 
        select
            Category_Relationship.Category_ChildID,
            Category_Relationship.Category_ParentID
        from
            Category_Relationship      
            inner join
                subchildren      
                on Category_Relationship.Category_ParentID = subchildren.Category_ParentID
    )
select * from subchildren
0
 
LVL 6

Author Comment

by:trickyidiot
ID: 22827268
Can it be set up to have subchildren as a temp table? This is going to be a stored proc, passing the parent ID
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22827317
something like this then

Select * from
    (
        select
            Category_ChildID,
            Category_ParentID
        from
            Category_Relationship
        where
            Category_ChildID = @Topid_in
 
        union all      
 
        select
            Category_Relationship.Category_ChildID,
            Category_Relationship.Category_ParentID
        from
            Category_Relationship      
            inner join
                subchildren      
                on Category_Relationship.Category_ParentID = subchildren.Category_ParentID
    ) as subchildren
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Author Comment

by:trickyidiot
ID: 22827339
Look at the inner join in that second query - it's referencing 'subchildren', which doesn't actually exist.

Ideas?
0
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 500 total points
ID: 22827375
Ok, one last try here

Select * from
    (
        select
            Category_ChildID,
            Category_ParentID
        from
            Category_Relationship
        where
            Category_ChildID = @Topid_in
 
        union all      
 
        select
            x.Category_ChildID,
            x.Category_ParentID
        from
            Category_Relationship   x
            Where Exists (Select * from Category_Relationship y
                                    Where x.Category_ParentID = y.Category_ParentID
                                         and  y.Category_ChildID = @Topid_in )
    ) as subchildren
0
 
LVL 6

Author Closing Comment

by:trickyidiot
ID: 31510978
Thanks :)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax issue with my Where Clause SQL 2012 20 38
SQL Sum of items in two tables not equal. 5 43
Freeze portion of datamart 2 21
Many to one in one row 2 35
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question