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 ??
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 ??
So Menu$0560 points to the row with MenuCd=560?
In this case: normalize your model. You're violating 1NF.
In this case: normalize your model. You're violating 1NF.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :
those, then, a smart code should go get all of the rest byitseld.. I think we need like a Cursor or something.. no ?
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' );those, then, a smart code should go get all of the rest byitseld.. I think we need like a Cursor or something.. no ?
ASKER
wait nevermind..it works...... I found a way with your code.
hmm, @Sample is just your given data..
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.