Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql menu table

Posted on 2012-08-24
6
Medium Priority
?
565 Views
Last Modified: 2012-08-28
hey guys i am building a menu for my web page

here my table called menu

id              parent_id              title                  url
1               null                       Products           Null
2                1                          Sub One            Null
3                2                           Product One      ~/Products.aspx

i have this query

select id, parent_id, title , Isnull(url, '~/#') as url from menu

one problem, i need the last sub menu to be ~/# because i have a onclick method on my menu

i will like the query to return all fields in the menu table and change my url coloum

if the url coloum is null then it must change to ~/# but if that row have product it must be null
0
Comment
Question by:JCWEBHOST
6 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38328299
I am having trouble understanding ... which of the two rows did you want the ISNULL() to work on?  Perhaps if you showed your expected results.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38328369
select isnull(url, '~/#') url
  from yourtable
0
 

Author Comment

by:JCWEBHOST
ID: 38328385
the rows ISNULL()  must work where parent_id is null and row where the id is not in parent_id
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38328422
so, something like this?
select case when url is not null then url
                   when parent_id is null then isnull(url, '~/#') 
                   when exists( select null from yourtable o where o.parent_id = t.id ) then url
                   else isnull(url, '~/#') 
     end url
  from yourtable t 

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38328444
select m1.id, m1.parent_id, m1.title , 
       case when m1.parent_id is null or m2.parent_id is null then '~/#' else url end url
from menu m1
left join (select distinct parent_id from menu) m2 on m1.id = m2.parent_id

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 38329765
So what is it you want to see as output? Does your example cover all possible criteria?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question