Click here to Skip to main content
14,695,325 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi there,

I have been racking my brains on this for a little while now. I am writing a category driven website for a friend and need a way to extract the number of levels the categories go down in a table.

To explain, instead of sticking to maybe 3 levels:

Main Category > Sub Category > Sub Sub Category

I want to be able to go down as far as possible but calling the categories out based on the data. I have two tables tblCat which holds the main data and tblscat which holds the sub categories for the main categories. The table tblscat has two fields catID and scatID, to add a sub category its ID is placed in the scatID field and the scatID of the category we are currently in is placed in the catID column.

This model allows us to go down as far as required but extracting it using sql is a bit of a trick

I have written this sql script which sort of works but the number of levels is determined in advance:

        c.catID         mainID,
        c.catName       mainCategory,
        sc.scatID       subCatID,
        sc.scatName     subCatName,
        sc1.sCatID      subCat1ID,
        sc1.scatName    subCat1Name,
        sc2.scatID      subCat2ID,
        sc2.scatName    subCat2Name,
        sc3.scatID      subCat3ID,
        sc3.scatName    subCat3Name
FROM tblcat c
        LEFT JOIN tblscat sc
          ON c.catID = sc.catID
        LEFT JOIN tblscat sc1
          ON sc.sCatID = sc1.catID
        LEFT JOIN tblscat sc2
          ON sc1.sCatID = sc2.catID
        LEFT JOIN tblscat sc3
          ON sc2.sCatID = sc3.catID

I would like to be able to go down to the maximum number of levels as determined by the data stored in the tblscat table. I am using a join on the same table 3 times to create 3 levels but I should be able to determine this by what is held in the catID column, but I just can't seem to work it out.

Any offers of help are greatly appreciated.

Updated 12-Jan-11 1:10am

SQL is poor in hierachical data.
This article may help :[^]

Also look into this : Building hierarchy using Recursive CTE[^]

This will give you an insight in how your problem can be solved.

Espen Harlinn 12-Jan-11 6:25am
5+ CTE - we're heading in the same direction
Hiren solanki 12-Jan-11 6:26am
Take a +5 for involving me in answer too.
Use Common Table Expressions:[^]


Building hierarchy using Recursive CTE[^] by Hiren Solanki

Espen Harlinn
Hiren solanki 12-Jan-11 6:27am
Take a 5.
Espen Harlinn 12-Jan-11 6:35am
Thanks Hiren Solanki, it's your answer after all :)
Thank you all for your help but after doing what Seven suggested and googling mysql recursion I found a different approach using nested categories at this link:[^]

This seems a much more fluid approach then to doing lots of self joins. Apparently this can create quite a large overhead in the end result.

Working with this method does exactly what I need. I have moved away from two tables and into just one. Each category has a left and right value based on its position within the hierarchy. The scripts that come with the tutorial work like a charm.

Many thanks all for your help.

Google for T-SQL Recursion[^]. You will find plenty of examples of what you are trying to do.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900