September 17, 2008 at 11:10 am
First off, let me just apologize for the fact that I'm an application developer, not a DB guy, and am not query-savvy.
That being said, our DB has a table that stores a tree structure of Product Categories, and I need to be able to construct a bread crumb string in a query. So, if I query any category by its ID, it will return a "concatenated string" that shows all the category levels/sublevels etc. Something like:
Computer Accessories / Cables / 6ft USB
The table uses GUIDs in a varchar column as a primary key. Each record has its primary key, a name, then a column to store the ID of its parent node. Top level nodes have a '0' for their ParentID.
How can I write a query to do this?
September 17, 2008 at 11:33 am
do a search for common table expressions (CTE).
I don't sql installed here to do a demo for you. But you'll find plenty of help on this site... and I'm sure that either articles or scripts were posted on that very same topic.
Happy search.
P.S. You might want to just select the data and do the concat on the application side (depending on what info you need to keep to do what you have to do)... If you're going to need the ID and Name of each category, it's kind of pointless to do the concat on the server on top of returning all that.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply