Need help on self referenced table's query

  • 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.

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

    Recursive Queries in SQL Server 2005

    [/url]

  • 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

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

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

  • 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.

  • 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