Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

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.

0
vbnetcoder
Asked:
vbnetcoder
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check this article:
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
0
 
RickCommented:
I'm not sure I understood the question.
Could you not do something like this:

Select CategoryID + '/' + CategoryName + '/' + ParentCategoryID [CategoryID]
From Categories
Where ...
0
 
vbnetcoderAuthor Commented:
rick:

This can be multi level categories each with a categoryname.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Can you post some sample data from your table.
0
 
SharathData EngineerCommented:
If you have data like in my sample, and looking for a tree structure from parent to child, you can try CTE.
declare @Categories table(CategoryID int,CategoryName varchar(100),ParentCategoryID int)
insert @Categories values (1,'Vehicle',null)
insert @Categories values (2,'Car',1)
insert @Categories values (3,'Sedan',2)

select * from @Categories
/*
CategoryID	CategoryName	ParentCategoryID
1	Vehicle	NULL
2	Car	1
3	Sedan	2
*/
;WITH CTE AS(
SELECT CategoryID, CategoryName, ParentCategoryID, convert(varchar(max),CategoryName) Tree
  FROM @Categories
 WHERE ParentCategoryID is null
 UNION ALL
SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID, p.Tree + ' / ' + convert(varchar(max),c.CategoryName)
  FROM @Categories c
  JOIN CTE p
    ON c.ParentCategoryID = p.CategoryID
)
SELECT * FROM CTE where CategoryName = 'Sedan' 
/*
CategoryID	CategoryName	ParentCategoryID	Tree
3	Sedan	2	Vehicle / Car / Sedan
*/

Open in new window

0
 
vbnetcoderAuthor Commented:
TY
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now