Need help on self referenced table's query

Question

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dbo.benyos SSCommitted Points: 1944 More actions · Answer 1

This article, right here on SSC, wil be most helpful:

Recursive Queries in SQL Server 2005

[/url]

Carleton SSCommitted Points: 1706 More actions · Answer 2

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

Carleton SSCommitted Points: 1706 More actions · Answer 3

By the way, if your data is more than 1 level deep, then a recursive join will be needed as Ben pointed out.

jasondeocampo SSC Rookie Points: 39 More actions · Answer 4

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. ^_^

Carleton SSCommitted Points: 1706 More actions · Answer 5

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.

jasondeocampo SSC Rookie Points: 39 More actions · Answer 6

Thanks a lot. It really helped. You guys are great!!! 🙂