vbnetcoder
asked on
SQL query
I am having a problem writing a SQL query...
I have a table named Categories with the following fields:
CategoryID
CategoryName
ParentCategoryID
I need to be able to pass the Category ID a retrieve it's path in the following format
example:
Vehicle/Car/Sedan
Where Vehicle is the top level category and Sedan is the bottom level with the category ID that was passed. ParentCategoryID establishes the relationships between the various categories.
I have a table named Categories with the following fields:
CategoryID
CategoryName
ParentCategoryID
I need to be able to pass the Category ID a retrieve it's path in the following format
example:
Vehicle/Car/Sedan
Where Vehicle is the top level category and Sedan is the bottom level with the category ID that was passed. ParentCategoryID establishes the relationships between the various categories.
I'm not sure I understood the question.
Could you not do something like this:
Select CategoryID + '/' + CategoryName + '/' + ParentCategoryID [CategoryID]
From Categories
Where ...
Could you not do something like this:
Select CategoryID + '/' + CategoryName + '/' + ParentCategoryID [CategoryID]
From Categories
Where ...
ASKER
rick:
This can be multi level categories each with a categoryname.
This can be multi level categories each with a categoryname.
Can you post some sample data from your table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TY
http://msdn.microsoft.com/en-us/magazine/cc794278.aspx
you can see how to create such a query, you could either go through it entirely, or start around Figure 5, where you can see the results of the query just above, using WITH syntax which can do exactly what you need