SQL Challenge... Trees?

[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