February 19, 2007 at 5:17 am
Hi,
I have a table that contains categoryid, categoryname and basecategoryid.
For every category, the parent categoryid is stored in the basecategoryid field.
Each category can have many levels of sub categories.
I want a query which will retrieve all the sub and sub sub categories( till the last level), given a categoryid.
Appreciate help in this regard.
regards,
Deepika.
February 19, 2007 at 6:15 am
What have you tried?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2007 at 9:20 pm
I am able to drill down only to one level.
I used the following query:
SELECT c1.categoryid as ParentCategoryID, c2.categoryid as SubCategoryID, c1.categoryname AS ParentCategory, c2.categoryname AS SubCatgeory
FROM category AS c1 INNER JOIN category AS c2 ON c1.categoryid=c2.basecategoryid
ORDER BY c1.categoryid;
February 20, 2007 at 12:53 am
you will need helper objects (#temptb) to solve this tree in sqlserver2000.
With sql2005 you would use a CTE.
Search SSC for "tree" and you'll find some options.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2007 at 7:07 am
Another place to look is under "Expanding Hierarchies" in Books OnLine...
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2007 at 2:07 pm
<>
Hi, have you try something called a cartesian operation (sorry im French and i m not sure it is the good translation), actually the aim is to make all the couple possibilities and then restrict with the where clause in order to check if the original record got a subcategory, ive made a query like this but ive got hole in my brain, i will try to find it and then i will post it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply