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:
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.