September 17, 2009 at 6:07 pm
Can someone give me a query string on how to get the following output on my self-referenced categories table?
Here are the table's fields with sample values:
CategoryID CategoryUID Name ParentCategoryID
1 a165c221 Accessories 0
2 27e1e196 Hats 1
3 31b17038 Scarfs 1
4 5311a80e Belts 1
5 7dd896a2 Hair 1
I want to arrive on this output:
Accessories
Accessories - Hats
Accessories - Scarfs
Accessories - Belts
Accessories - Hair
Any reply would be greatly appreciated.
September 18, 2009 at 3:09 am
This query will yield the results you seek:
Select a.[Name], b.[Name]
FROM [YourTable] a
INNER JOIN [YourTable] b on a.categoryid = b.ParentCategoryID
September 18, 2009 at 3:12 am
By the way, if your data is more than 1 level deep, then a recursive join will be needed as Ben pointed out.
September 21, 2009 at 9:04 am
Guys thanks a lot for the reply. I have another problem. What if I have these set of Data:
CategoryID - CategoryUID - Name - ParentCategoryID - Title
1 - a165c221 - Accessories - 0 - NULL
2 - 27e1e196 - Hats - 1 - NULL
3 - 31b17038 - Scarfs - 1 - NULL
4 - 5311a80e - Belts - 1 - NULL
5 - 7dd896a2 - Hair - 1 - NULL
I want to Update the Title field to:
Accessories
Accessories - Hats
Accessories - Scarfs
Accessories - Belts
Accessories - Hair
Need Help. THanks a lot. ^_^
September 21, 2009 at 9:34 am
Here ya go:
UPDATE a SET a.title = a.[Name] + ' - ' + b.[Name]
FROM [MyTable] a
INNER JOIN [MyTable] b ON b.categoryid = a.ParentCategoryID
Job saving tip: make sure you test this before running in your prod env.
September 21, 2009 at 9:51 am
Thanks a lot. It really helped. You guys are great!!! 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply