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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy