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??

[pnish]

This isn't too hard to do in VB. Depending on how deeply nested the data set is I would use recursion, ie a routine that calls itself.

Public Sub GetCat(ByVal cat As Long)
    
    Dim rs As Recordset
    Dim catid As Long
    Dim sql As String
    sql = "select * from [cat] where [CatID]=" & cat
    
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
        
    If rs.RecordCount > 0 Then
        Debug.Print rs!catid, rs!cattext, rs!supercatid
        If IsNull(rs!supercatid) Then
            catid = -1
        Else
            catid = rs!supercatid
        End If
        rs.Close
        Set rs = Nothing
        GetCat catid    ' Re-cursive call
        Exit Sub
    End If
    
    rs.Close
    Set rs = Nothing
    
End Sub

So if you wanted category 4 and all its children you would call the GetCat sub with a value of 4. This bit of code is using DAO but it could just as easily use ADO if you're accessing SQL Server etc.

This code also isn't very efficient, opening & closing the data source with each iteration. I'm sure you'll find a better way to do it.

Cheers

[rjlohan]

Probably a faster way, along the same lines would be to retrieve the whole table (shouldn't be too big) and sort a disconnected recordset, or array.

I was hoping for a SQL query that would cover this though...

[rjlohan]

Hmmm no luck yet. Have tried shaped recordsets, but can't work out how to get the right tree...

[pnish]

Originally posted by rjlohan
Hmmm no luck yet. Have tried shaped recordsets, but can't work out how to get the right tree...

Are you still trying to do this with a SQL query? I haven't had heaps of experience with complex queries (which I think this would be) so I'm not sure I can help other than to point you back to the code I posted earlier.

I know this code works (well it worked on the small data set I tested it against anyway).

There's gotta be a SQL guru on these forums somewhere.

Cheers

[rjlohan]

I can do it with code, but I don't want to if I can get away with it.

[pnish]

When I get a spare moment I'll see if I can come up with any ideas. In the meantime, don't hold your breath!

I'm sure that recursion is the secret. It's just a matter of figuring out a way of doing it in SQL, a stored procedure perhaps.

As I said, I'm no SQL guru, but there's gotta be a way.

Cheers

[rjlohan]

Cool. Good luck. I'm still pluggin'.

[John]

What we do in vBulletin is we store a 'parent list' field. This contains a comma delimited list of ids of all the parent categories.

So you might have:

catID	catText	parentList
1	General	,1,
2	Test1	,1,2,
3	Test2	,1,3,
4	Test3	,1,2,4,

Then the parent list field can be used to grab all the parents of a particular category.

[rjlohan]

That's a possibility, although causes a fair bit of data redundancy. I will consider it though. Cheers.

[Sundance Kid V2]

I had the same problem once... but this www helped me allot. A small change to the db and it works 10000%... and fast.

http://www.intelligententerprise.com...celko1_1.shtml

Study the example... look weird in the begining... but its great !

That is not the exact one i wanted to show you.. but here is one more

http://www.aspalliance.com/Mamanze/a...h=sqlrecursive

I'll post the 100% correct one (if i can find it)

[rjlohan]

That looks like it may be what I want.

Cheers,
RJ

[rjlohan]

After much stuffing around, have gotten really sick of this one, and fallen back on this solution.

As catID is an AutoNumber field, no new category can have a superCatID > catID.

So, I've decided the easiest solution is to just retrive the recordset order in reverse catID, from the requested catID and lower.

Then, with one pass, I can build the tree.

Simple. Wish I'd though of it before...