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 ??
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Not weird. Simply wrong.

DECLARE @Sample TABLE
(   
    [User] INT NOT NULL,
    MenuCd NVARCHAR(255) NOT NULL,
    OptionCd NVARCHAR(255) NOT NULL,
    [Description] NVARCHAR(255) NOT NULL,
    Name NVARCHAR(255) NOT NULL PRIMARY KEY
);

INSERT INTO @Sample
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.
0
 
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.
0
 
ste5anSenior DeveloperCommented:
So Menu$0560 points to the row with MenuCd=560?

In this case: normalize your model. You're violating 1NF.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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.
0
 
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 :

INSERT INTO @Sample
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 ?
0
 
PhilippeRenaudAuthor Commented:
wait nevermind..it works...... I found a way with your code.
0
 
ste5anSenior DeveloperCommented:
hmm, @Sample is just your given data..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.