Solved

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

Posted on 2008-10-28
6
1,376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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