SQL Query Parent Child Full Path from table

Question

I have a table listing the parent child relationship for each element like this:

ParentID    ParentTitle ChildId ChildTitle
----------------------------------------------
  843       Documents   38737   Jobs    
  843       Documents   52537   Tools
  843       Documents    5763   SecondOps
  843       Documents    4651   Materials
38737       Jobs        16619   Job001
38737       Jobs        16620   Job002
38737       Jobs        16621   Job003
38737       Jobs        16622   Job004
38737       Jobs        16623   Job005
52537       Tools        1952   HandTools
52537       Tools        1953   Automated
52537       Tools        1957   Custom
 1952       HandTools      12   Cordless10mm
 1952       HandTools      13   Cordless8mm
 1952       HandTools      14   CableCrimp
 1952       HandTools      15   Cutter
 1952       HandTools      16   EdgePlane
 5763       SecondOps     101   Procedure001
 5763       SecondOps     102   Procedure002
 5763       SecondOps     103   Procedure003
 4651       Materials   33576   Raw
 4651       Materials   33577   Mixed
 4651       Materials   33578   Hybrid
 4651       Materials   33579   Custom
16622       Job004        101   Procedure001
16622       Job004         14   CableCrimp
16622       Job004         15   Cutter
16622       Job004       4651   Mixed
16623       Job005        102   Procedure002
16623       Job005        103   Procedure003
16623       Job005      16619   Job001
16623       Job005       1953   Automated
16623       Job005      33579   Custom
16623       Job005      33576   Raw

I would like to get the full path of each Combination using the IDs, for example

Documents\Jobs\Job003 = 843\38737\16621

Another example would be "Procedure001" which is listed in 2 places

Documents\SecondOps\Procedure001 = 843\5763\101

The same document is also referenced here:

Documents\Jobs\Job004\Procedure001 = 843\38737\16622\101

I'd like to take this table and build a TreeView in .NET. So having the full path for each item would make it a cake walk.

Otherwise, I was thinking that I could start at the Root page and keep recursing through the parents, building a child list, then recursing those, etc.

Is there a better way to query this to build those paths? This list has 400,000 records so if there is a more efficient way it would save time

This was all originally in an AS400 system DB until 2000ish then made into a MediaWiki site. I am pulling the data via the api with the intent of building an interface for a SQL Server database.

I can do basic SQL queries, joins, unions, etc.

Let me know what other info I can provide if this isn't clear

Answer 1

A tree structure means Recursion for a generic solution. Pls, don't try this in sql. Just take datarow from sql into a list or something like and make populate with recursion in a programming language.

Your tree class wil be like :

public class MyObj {
    public int Id {get; set;}
    public string Title {get; set;}
    public List<MyObj> {get; set; } = null;
}

0.You table its pretty wrong. The corect way will be :

 CREATE TABLE Jobs(
   Id int not null primary key,
   Title nvarchar(255) not null,
   StartTime datetime,--optional maybe will help
   ParentId int null --can be null root will have no parent

   )

But I will try to explain on your table how it's done. I will suppose that you have some kind datacontext (DBML,EDMX etc.)

1. Find root or roots. In your case root will those nr that are on ParentID and are not on the ChildId.

Query that will list your roots:

SELECT DISTINCT a.ParentId FROM
YourTable a LEFT JOIN 
YourTable b ON a.ParentId=b.ChildId
WHERE b.ParentId is null 

2. Make a recursive procedure that will retrive your data in a class structure as above(MyObj).

   procedure MyObj GetTree(int id, db){ if (db.YourTable.Any(r => r.ParentId==Id)){

         var q=db.YourTable.Select(r => r.ParentId==Id).ToList();
         var result = new MyObj{
             Id = q.ParentId,
             Title = q.ParentTitle,
             Children = new List<MyObj>()   
         }
         foreach( var el in q) {
             if (db.YourTable.Any(r => r.ParentId==el.ChildId))
             result.Children.Add(GetTree(el.ChildId,db))
             else 
             result.Children.Add( new MyObj{
              Id = el.ChildId,
             Title = el.ChildTitle,
             Children = null 
              });
             return result;
         }
   
    }
    return null;
   

   }

3. make trees with list Id from point 1 stored in a list let's say ListIds you will do something like that:

   List finaltrees = new List()

   Ids.ForEach(id => finaltrees.Add(GetTree(id,dbcontext));

Now you have a tree structure in finaltrees.

PS:

I wrote the code directly in browser (C#),there can be some typos error.

Answer 2

So to elaborate on what I am trying to do, I'm working with a wiki version that doesn't use namespaces to establish document paths.

For example if a page is 3 levels deep on a document tree like this

• RootPage
  • Page01
    • Page02
      • Page03
      • Page04

Using the Namespace approach Page03's Name(Path) is "RootPage:Page01:Page02:Page03"

I would Like to do the same thing with the PageIDs

So given this example you would have

• PageTitle PageId Path
• RootPage 001 001
• Page01 101 001:101
• Page02 201 001:101:201
• Page03 301 001:101:201:301
• Page04 302 001:101:201:302

So now All I have to do is Put the PagePath together.

There are several challenges to Consider with this wiki

1. No 2 documents can have the same TITLE
2. Document IDs are basically irrelevant, but handy in this case(at least in the version I am working on)
3. Thankfully there is a list of Pages and their "Links" or Child Pages. I believe you would call it a MANY to MANY

The Key Point to remember is even if a page is listed as a child of many other pages, Only one really exists and I only need one of them in the results.

So Using LONG's example here is where I've gotten to

Using this Table:

CREATE Table [dbo].[ExampleTable](
[RecordID] Int IDENTITY (1, 1) Not NULL,
[ParentID] Int Not NULL,
[ParentTitle] VARCHAR(800) NULL,
[ChildID] Int Not NULL,
[ChildTitle] VARCHAR(800) NULL,
PRIMARY KEY CLUSTERED ([RecordID] ASC),);

This Data:

INSERT INTO [dbo].[ExampleTable]
([ParentID]
,[ParentTitle]
,[ChildID]
,[ChildTitle])
VALUES
(843,'Documents',38737,'Jobs'),
(843,'Documents',52537,'Tools'),
(843,'Documents',5763,'SecondOps'),
(843,'Documents',4651,'Materials'),
(38737,'Jobs',16619,'Job001'),
(38737,'Jobs',16620,'Job002'),
(38737,'Jobs',16621,'Job003'),
(38737,'Jobs',16622,'Job004'),
(38737,'Jobs',16623,'Job005'),
(52537,'Tools',1952,'HandTools'),
(52537,'Tools',1953,'Automated'),
(52537,'Tools',1957,'Custom'),
(1952,'HandTools',12,'Cordless10mm'),
(1952,'HandTools',13,'Cordless8mm'),
(1952,'HandTools',14,'CableCrimp'),
(1952,'HandTools',15,'Cutter'),
(1952,'HandTools',16,'EdgePlane'),
(5763,'SecondOps',101,'Procedure001'),
(5763,'SecondOps',102,'Procedure002'),
(5763,'SecondOps',103,'Procedure003'),
(4651,'Materials',33576,'Raw'),
(4651,'Materials',33577,'Mixed'),
(4651,'Materials',33578,'Hybrid'),
(4651,'Materials',33579,'Custom'),
(16622,'Job004',101,'Procedure001'),
(16622,'Job004',14,'CableCrimp'),
(16622,'Job004',15,'Cutter'),
(16622,'Job004',4651,'Mixed'),
(16623,'Job005',102,'Procedure002'),
(16623,'Job005',103,'Procedure003'),
(16623,'Job005',16619,'Job001'),
(16623,'Job005',1953,'Automated'),
(16623,'Job005',33579,'Custom'),
(16623,'Job005',33576,'Raw')
GO

And This Query, Which I modified from LONG's example:

SELECT DISTINCT C.ChildTitle as PageTitle, convert(varchar(20),A.ParentID) + ':' + convert(varchar(20),B.ParentID) + 

CASE WHEN C.ParentID IS NOT NULL THEN ':' + convert(varchar(20),C.ParentID)
ELSE ''
END

+

CASE WHEN C.ChildID IS NOT NULL THEN ':' + convert(varchar(20),C.ChildID)
ELSE ''
END

FROM ExampleTable AS A 
INNER JOIN ExampleTable AS B
ON B.ParentID = A.ChildId
LEFT JOIN ExampleTable AS C
ON C.ParentID = B.ChildId
ORDER By PageTitle

I get These Results:

PageTitle           UnNamed
NULL        16622:4651
NULL        38737:16622
NULL        38737:16623
NULL        52537:1952
NULL        843:38737
NULL        843:4651
NULL        843:52537
NULL        843:5763
Automated   843:38737:16623:1953
CableCrimp  843:38737:16622:14
CableCrimp  843:52537:1952:14
Cordless10mm    843:52537:1952:12
Cordless8mm 843:52537:1952:13
Custom      38737:16622:4651:33579
Custom      843:38737:16623:33579
Cutter      843:38737:16622:15
Cutter      843:52537:1952:15
EdgePlane   843:52537:1952:16
Hybrid      38737:16622:4651:33578
Job001      843:38737:16623:16619
Mixed       38737:16622:4651:33577
Mixed       843:38737:16622:4651
Procedure001    843:38737:16622:101
Procedure002    843:38737:16623:102
Procedure003    843:38737:16623:103
Raw     38737:16622:4651:33576
Raw     843:38737:16623:33576

What I'd like to get is a SINGLE occurance of each page, Regarless of which Parent it happens to be found

Then I can use these Paths to turn the Virtual Tree Structure into an actual Tree Structure.

The Last Issue is that the actual Link List is VERY similar to the example I created, except that it has 400,000 records.

When I run this query against the actual "Link List" it runs for about 17 minutes and runs out of memory.

I've been researching the MAXRECURSION option, but I am still working on it, don't know if that is problem or not.

Answer 3

You could use INNER JOIN and LEFT JOIN if you are using SQL SERVER MS, and here are how the query look like, which will give you the full result (combination) based on your requirement:

SELECT A.ParentTitle + '\'+B.ParentTitle+ 

                                         CASE WHEN C.ParentTitle IS NOT NULL THEN '\' +C.ParentTitle
                                         ELSE ''
                                         END
     +
     ' =' + A.ParentID + '\'+B.ParentID+ 

                                         CASE WHEN C.ParentID IS NOT NULL THEN '\' +C.ParentID
                                         ELSE ''
                                         END


FROM TABLE AS A 
INNER JOIN TABLE AS B
ON B.ParentID = A.ChildId
LEFT JOIN TABLE AS C
ON C.ParentID = B.ChildId

Not 100% sure whether it will work as I expected or not, please give it a try xD