Solved

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

Posted on 2008-10-28
6
1,367 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:xDJR1875
Comment Utility
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
Comment Utility
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:xDJR1875
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Author Comment

by:trickyidiot
Comment Utility
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:
xDJR1875 earned 500 total points
Comment Utility
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
Comment Utility
Thanks :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now