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.

vbnetcoderAsked:
Who is Participating?
 
SharathConnect With a Mentor Data 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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vbnetcoderAuthor Commented:
rick:

This can be multi level categories each with a categoryname.
0
 
SharathData EngineerCommented:
Can you post some sample data from your table.
0
 
vbnetcoderAuthor Commented:
TY
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.