SQL Challenge... Trees?

[rjlohan]

I want to store in a single table in a database, a sort of tree structure. I have the relationships and the table all set up, but I want to be able to retrieve data in a special way.

Table Schema:
[Categories]
catID: PK
catText
superCatID: FK (Relates to catID, is not compulsory)


So, in a table, I may have the following data:

catID catText superCatID
1 General
2 Test1 1
3 Test2 1
4 Test3 2


Now, if the user wants category 4, I want to retrieve the entire chain that 4 inherits:
4 Test3 2
2 Test1 1
1 General


As you can see, catID(2) is the parent of catID(4), and catID(1) is the parent of catID(2).





Now, can this be done??

[visualsander]

rjlohan,

This is a employees table.
every employee has a empid and a manager. except the CIO his mgr =NULL

select name,
empID,
mgr, --manager
LEVEL
from employees
START WITH
mgr IS NULL
CONNECT BY
PRIOR empID=mgr;

This generates a tree:
root
Parent
Child
Child
Parent
Child
Parent
Child
...
...
...

Oh, I've used this in oracle. don't know if it works in VB. Let me know.
Check it out.

visualsander

[rjlohan]

I kind of follow that. Not sure how much is Oracle specific SQL though.

Would you be able to explain the query terms a little more to me?

That would be great!


Also, could you perhaps just show me the table schema, so I can follow the field names and stuff?

Cheers,
RJ

[Gary.Lowe]

Ryan

Have you tried shaped recordsets.

Data shaping enables you to define the columns of a shaped Recordset, the relationships between the entities represented by the columns, and the manner in which the Recordset is populated with data.

Columns of a shaped Recordset may contain data from a data provider such as Microsoft SQL Server, references to another Recordset, values derived from a calculation on a single row of a Recordset, values derived from an operation over a column of an entire Recordset; or they may be a newly fabricated, empty column.

When you retrieve the value of a column that contains a reference to another Recordset, ADO automatically returns the actual Recordset represented by the reference. A Recordset that contains another Recordset is called a hierarchical recordset. Hierarchical recordsets exhibit a parent-child relationship, in which the parent is the containing recordset and the child is the contained recordset. The reference to a Recordset is actually a reference to a subset of the child, called a chapter. A single parent may reference more than one child Recordset.

The shape command syntax enables you to programmatically create a shaped Recordset. You can then access the components of the Recordset programmatically or through an appropriate visual control. A shape command is issued like any other ADO command text.

You can make hierarchical Recordset objects in two ways with the shape command syntax. The first appends a child Recordset to a parent Recordset. The parent and child typically have at least one column in common: the value of the column in a row of the parent is the same as the value of the column in all rows of the child.

The second way generates a parent Recordset from a child Recordset. The records in the child Recordset are grouped, typically using the BY clause, and one row is added to the parent Recordset for each resulting group in the child. If the BY clause is omitted, the child Recordset will form a single group and the parent Recordset will contain exactly one row. This is useful for computing "grand total" aggregates over the entire child Recordset.

Regardless of which way the parent Recordset is formed, it will contain a chapter column that is used to relate it to a child Recordset. If you wish, the parent Recordset may also have columns that contain aggregates (SUM, MIN, MAX, and so on) over the child rows. Both the parent and the child Recordset may have columns which contain an expression on the row in the Recordset, as well as columns which are new and initially empty.

You can nest hierarchical Recordset objects to any depth (that is, create child Recordset objects of child Recordset objects, and so on).

You can access the Recordset components of the shaped Recordset programmatically or through an appropriate visual control.

Microsoft provides a visual tool that generates shape commands (see The Data Environment Designer in the Visual Basic documentation) and another that displays hierarchical cursors (see Using the Microsoft Hierarchical Flexgrid Control in the Visual Basic documentation).

[rjlohan]

Cheers.

Will check that out at work tomorrow.

[rjlohan]

Just bumping this thread while I look into Gary's idea. Other suggestions are welcome.

[rjlohan]

Hmmm... no luck. Still can't construct it correctly...

CatID ParentCatID
1
2 1
3 1
4 2




If I add the parameter catID = 4, I only get catID 4 & 2. But not 1, which is the parent of 2...

If I add no paremeter, I just get the whole table.

Any ideas

[Gary.Lowe]

Ahhh I think I know what you mean.


I developed a catering system which had a tree like this

Category Description Parent
1 Food NULL
2 Beverage NULL
3 Meat 1
4 Chicken 3
5 Beef 3
6 Spirits 2
7 Vodka 6
8 Whiskey 6
9 Wine 2
10 Red 9
11 Rose 9
12 White 9


Now what I wanted to do was find out what the true parant category was for reports i.e. All the Beverage that was sold.

So Red Wine has a category of 11 and parent of 9 but the true parent is actually 2.

What I did was to create a category parent table which I would add records on the fly (incase someone moved categories around.

I first would delete everything out of the table

Then create a recordset that had just the category number and the category parent

SELECT PrdCatNo, PrdCatParent FROM tblProductCategories

I would then use the following code to loop through the recordset and find the true parent category.

This I would then insert into the table which made it very easy to group the records the way I required.

Do Until rsnpCategoryNo.ActiveRecordset.EOF
        'Set the search category number to the category number
        'in the recordset
        varSearchCatNo = rsnpCategoryNo.ActiveRecordset.Fields("PrdCatNo").Value
        varParentCategoryNo = rsnpCategoryNo.ActiveRecordset.Fields("PrdCatParent").Value
        Do Until varParentCategoryNo = ""
            'Loop through until the parent category is null
            If Not gedbMain.GetField("SELECT PrdCatNo, PrdCatParent FROM tblProductCategories WHERE PrdCatNo=" & _
                varSearchCatNo, exOpenStatic, varCategoryNo, varParentCategoryNo) Then
                Exit Do
            End If
            varSearchCatNo = varParentCategoryNo
        Loop
         
        strSQL = "INSERT INTO" & _
                    " tblTillCategories (TllCatNo, TllCatParent) " & _
                " VALUES" & _
                    " (" & rsnpCategoryNo.ActiveRecordset.Fields("PrdCatNo").Value & ", " & varCategoryNo & ")"
        
        If Not gedbMain.ExecSQL(strSQL, False) Then
            gerrError.PopErrorStack
            Exit Sub
        End If
        
        'Move to next record
        If Not rsnpCategoryNo.MoveRecord(exMoveNext) Then
            Exit Do
        End If
    Loop

There maybe an easier way of doing this but I know this works and isn't that intensive.

Hope this is of use Ryan

[rjlohan]

Ok, that's an option. I'll think about that.

MY main worries are data redundancy, and repeated queries, but something like this might work.

Am currently reading up on another potential solution someone found.

Cheers Gary.

[Lightning]

Okay, it is an old (very old) thread, but I have struggled with this some times, and I know it is a fairly common problem, and I found a pretty cool sollution, 1 query , no SP, but works (I think) only with msSQL

declare @PrevInChain
int  declare @ChainList table(chainID int)  
SET @PrevInChain=  123456 --The last record 
WHILE (@PrevInChain is not null)  
BEGIN  
    insert into @ChainList select PreviousID FROM T_Table WHERE currentID = @PrevInChain 
    select @PrevInChain = (select PreviousID FROM T_Table WHERE currentID = @PrevInChain )  
END   
Select * from T_Table where CurrentID in (select * from @ChainList)

I hope someone can use this

[vb_dba]

If there was some sort of Maximum on the number of levels, you could always join the table to itself:

--Based on Gary.Lowe's example data
--This will get down to the 3rd level, ie) Beverage/Wine/White
Select t1.[Description]'Parent'
     , t2.[Description]'Child 1'
     , t3.[Description]'Child 2'
From tblCat t1 Left Join tblCat t2 On t1.[Category] = t2.[Parent]
               Left Join tblCat t3 On t2.[Category] = t3.[Parent]

Not sure if it would be very useful, but you asked for suggestions...