Solved

Parent-Child Tree structure.

Posted on 2002-06-03
5
406 Views
Last Modified: 2006-11-17
Hello, I need help with a little (but hard for me) problem.  Below is a locations code table and has the following tree rollup.
<pre>
                     0000
                       |
                     0100
                     /   \____
                  8452        \
                   / \         9718
              6107 6108          |____
                 |    |              8205
                 |   6511          /   |   \
                 |   / \         3704 3709 3888
                 2000  6518             |
                        /\             2110
                     2510 2597          / \
                                     2201 2202
                                       /
                                     2203
</pre>
Parent     Child   PCT
0000     0110     100
0110     8452     100
8452     6107     100
8452     6108     100
6107     2000     50
6108     6511     100
6511     2000     50
6511     6518     100
6518     2510     100
6518     2597     100
0110     9718     100
9718     8205     100
8205     3704     100
8205     3709     100
8205     3888     100
3888     2110     100
2110     2201     100
2110     2202     100
2201     2203     100

What my issue is, I need to get a child list from any point on the tree.  So for example if I enter 8205 the I need a list of all childs under that code.

I created a Relationships table with the following columns.

TABLE LOCREL
------------
Parent nvarchar(4)
Child nvarchar(4)
PCTRESP float


Please help!  Ultimately I will need to check for duplicates, be able to do structural moves (i.e. 8205 and all childs (structure intact) say under 2510 now (as an example)..  But I will try to do this with more questions.



Thanks in advance!
0
Comment
Question by:ryderjam
  • 3
  • 2
5 Comments
 

Author Comment

by:ryderjam
ID: 7051348
.....................0000
.......................|
.....................0100
...................../...\_____
..................8452.........\
.................../.\.........9718
..............6107.6108...........|
.................|....|..........8205
.................|...6511....../...|...\
.................|.../.\......3704.3709.3888
.................2000..6518.............|
......................../\.............2110
.....................2510.2597........../.\
.....................................2201..2202
....................................../
.....................................2203
0
 

Author Comment

by:ryderjam
ID: 7051349
my drawing doesnt appear to working out! but the table describes it .
0
 
LVL 1

Accepted Solution

by:
Brighton earned 200 total points
ID: 7051518
Try this

DECLARE @Start_Iden      NUMERIC(12)

SET @Start_Iden = 8205

BEGIN

  SET NOCOUNT ON

  DECLARE @Generation_Count     NUMERIC(12)
  DECLARE @RowCount          NUMERIC(12)
  DECLARE @Start_Level          NUMERIC(12)
  DECLARE @End_Level          NUMERIC(12)
  DECLARE @RC                NUMERIC(12)

  SET @Generation_Count = 1

  SELECT @RC = OBJECT_ID('tempdb..#RESULT_List')

  IF  ISNULL( @RC,0 ) <> 0
  BEGIN
      DROP TABLE #RESULT_List
  END
  SELECT @RC = OBJECT_ID('tempdb..#PARENT_List')

  IF  ISNULL( @RC,0 ) <> 0
  BEGIN
      DROP TABLE #PARENT_List
  END

  SELECT a.parent,
        a.child,
      NULL as 'level'
   INTO #RESULT_List
    FROM Parent_Child_Table a  -- Place the name of your table here
   WHERE a.child      = @Start_Iden

  SELECT top 0 a.parent,
        a.child,
      NULL as 'level'
  INTO #PARENT_List
    FROM Parent_Child_Table a
   WHERE a.child      = @Start_Iden

Next_Iteration:

   INSERT INTO #PARENT_LIST
   SELECT parent,
       child,
       @Generation_Count AS 'level'
     FROM #RESULT_LIST

  INSERT INTO #RESULT_LIST
  SELECT a.parent,
      a.child,
            @Generation_Count
    FROM #PARENT_LIST JOIN Parent_Child_Table a
      ON a.parent=#PARENT_List.child
   WHERE NOT EXISTS( SELECT child FROM #RESULT_List b
                           WHERE b.parent = a.parent )

  SELECT @RowCount = @@ROWCOUNT

  SELECT @Generation_Count = @Generation_Count + 1

  IF @RowCount > 0
  BEGIN
     TRUNCATE TABLE #PARENT_List
        GOTO Next_Iteration
  END


SELECT DISTINCT * FROM #RESULT_List order by level
END
0
 
LVL 1

Expert Comment

by:Brighton
ID: 7051574
You may even try this if you want to limit the number of generations to go down.


SELECT  DISTINCT LVL1.Parent AS 'LVL0',
     LVL1.Child AS 'LVL1',
     LVL2.Child AS 'LVL2',
     LVL3.Child AS 'LVL3',
     LVL4.Child AS 'LVL4',
     LVL5.Child AS 'LVL5',
     LVL6.Child AS 'LVL6',
     LVL7.Child AS 'LVL7'

FROM Parent_Child_Table LVL1 LEFT JOIN Parent_Child_Table LVL2 ON
     (LVL1.Child = LVL2.Parent )
     LEFT JOIN Parent_Child_Table LVL3 ON
     (LVL2.Child = LVL3.Parent )
     LEFT JOIN Parent_Child_Table LVL4 ON
     (LVL3.Child = LVL4.Parent )
     LEFT JOIN Parent_Child_Table LVL5 ON
     (LVL4.Child = LVL5.Parent )
     LEFT JOIN Parent_Child_Table LVL6 ON
     (LVL5.Child = LVL6.Parent )
     LEFT JOIN Parent_Child_Table LVL7 ON
     (LVL6.Child = LVL7.Parent )
WHERE LVL1.Parent = 8205
ORDER BY LVL1.Child,LVL2.Child,LVL3.Child,LVL4.Child,LVL5.Child,LVL6.Child,LVL7.Child
0
 
LVL 1

Expert Comment

by:Brighton
ID: 7051585
My first post products the following output
parent         child              level      
-------------- -------------- -----------
9718           8205           NULL
8205           3704           1
8205           3709           1
8205           3888           1
3888           2110           2
2110           2201           3
2110           2202           3
2201           2203           4

My second post displays this

LVL0           LVL1           LVL2           LVL3           LVL4           LVL5           LVL6           LVL7          
-------------- -------------- -------------- -------------- -------------- -------------- -------------- --------------
8205           3704           NULL           NULL           NULL           NULL           NULL           NULL
8205           3709           NULL           NULL           NULL           NULL           NULL           NULL
8205           3888           2110           2201           2203           NULL           NULL           NULL
8205           3888           2110           2202           NULL           NULL           NULL           NULL
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Noob question:this site is sql vulns? 2 45
Numeric sequence in SQL 14 37
Sql query 34 19
Stored procedure 23 9
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

757 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

23 Experts available now in Live!

Get 1:1 Help Now