a dynamic way to extract the number of categories from a table

See more:

ASP

SQL

MySQL

T-SQL

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:

SELECT
        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
ORDER BY c.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.

Cheers
Graham