Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-28
6
Medium Priority
?
1,385 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

578 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