SQL Query Recursive

Hello SQL experts,

I am doing a select query that will return data like this :

User            MenuCd            OptionCd          Description              Name
456            0550                     0010                  Test                           Menu$0560
456            0550                     0020                  Test2                         www.google.ca
456            0550                     0410                  New page                Menu$0570
and so on

Where I need your help is, this select does NOT return all recursive menus. For example, this select was for the menu "0550" that you see in column MenuCd. when you see the column "Name" sometimes you have a web site, but sometimes its "Menu$XXXX"
my query should be smart enough to return more rows with those menuCd of that Menu$

so the end result would look like this :

User            MenuCd            OptionCd          Description                Name
456            0550                     0010                  Test                             Menu$0560
456            0550                     0020                  Test2                           www.google.ca
456            0550                     0410                  New page                   Menu$0570
456            0560                     0010                  page in 0560              www.amazon.ca
456            0560                     0020                 page2                            Menu$0599
456            0570                     0010                page in 0570                 www.1234.com
456            0599                     1416               page in 0599                  www.6789.com

so you see, everytime you see a Menu$ we shouldg et all the information of it in that same query
there could be many levels in one (its like a menu with sub menus)

The column "Option" is just a unique code by menuCd thats why it restarts at 0010 sometimes.. it could be anything.

can you help ??
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
Without knowing your database schema, it's going to be tough to help you write a query.

But what you are looking for is called a "Hierarchical Query".

If you provide more information about your schema, we can help you write up a query.
Or, if you just want to research Hierarchical Queries on your own, then you can try to create the query yourself.
ste5anSenior DeveloperCommented:
So Menu$0560 points to the row with MenuCd=560?

In this case: normalize your model. You're violating 1NF.
PhilippeRenaudAuthor Commented:
Ste5an ....  Yes its point to that,
but, its not that "easy" to fix to not Violate 1NF anymore, its an old structure software/database of a company and they will not change this now.
I have to find a way to get the data with a select Query, even if the code looks weird.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

ste5anSenior DeveloperCommented:
Not weird. Simply wrong.

    [User] INT NOT NULL,
    MenuCd NVARCHAR(255) NOT NULL,
    OptionCd NVARCHAR(255) NOT NULL,
    [Description] NVARCHAR(255) NOT NULL,

VALUES  ( 456, '0550', '0010', 'Test', 'Menu$0560' ),
        ( 456, '0550', '0020', 'Test2', 'www.google.ca' ),
        ( 456, '0550', '0410', 'New page', 'Menu$0570' ),
        ( 456, '0560', '0010', 'page in 0560', 'www.amazon.ca' ),
        ( 456, '0560', '0020', 'page2', 'Menu$0599' ),
        ( 456, '0570', '0010', 'page in 0570', 'www.1234.com' ),
        ( 456, '0599', '1416', 'page in 0599', 'www.6789.com' );

WITH    Normalized AS
            SELECT  S.*,
                    IIF(SUBSTRING(S.Name, 1, 5) = 'Menu$', SUBSTRING(S.Name, 6, 255), NULL) AS ChildMenuCd
            FROM    @Sample S
        Hierarchy AS
            SELECT  A.*,
                    0 AS [Level],
                    '\\' + CAST(A.MenuCd AS NVARCHAR(MAX)) AS [Path]
            FROM    Normalized A
            WHERE   NOT A.ChildMenuCd IS NULL
            UNION ALL
            SELECT  C.*,
                    [Level] + 1,
                    P.[Path] + '\' + C.MenuCd
            FROM    Normalized C
                INNER JOIN Hierarchy P ON C.MenuCd = P.ChildMenuCd
    SELECT  *,
            REPLICATE(' ', 8 * H.[Level]) + H.[Name]
    FROM    Hierarchy H
    ORDER BY H.[Path] ASC;

Open in new window

but, its not that "easy" to fix to not Violate 1NF anymore, its an old structure software/database of a company and they will not change this now.

Did you explain to them the additional costs of this error? Also the problems about data integrity?

Just a story I've seen happening: They run a database violating those rules, they also refused to use relationships and constraints to avoid "cryptic" error messages. And what happend in the end? The server was shutdown after two years and the disks where disposed. Cause no more information could be retreived from it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhilippeRenaudAuthor Commented:
Ste5an, i understand your points, I really do.

But hold on, for your code you are helping me with... the problem is that you are already entering in your @Sample table the other levels, for instance $0599

the problem is, (thats why I called it recursivce) is that your sample table shoudl start with only :

VALUES  ( 456, '0550', '0010', 'Test', 'Menu$0560' ),
        ( 456, '0550', '0020', 'Test2', 'www.google.ca' ),
        ( 456, '0550', '0410', 'New page', 'Menu$0570' );

Open in new window

those, then, a smart code should go get all of the rest byitseld.. I think we need like a Cursor or something.. no ?
PhilippeRenaudAuthor Commented:
wait nevermind..it works...... I found a way with your code.
ste5anSenior DeveloperCommented:
hmm, @Sample is just your given data..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.