Solved

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

Posted on 2008-10-28
6
1,375 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

736 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