sql menu table

Posted on 2012-08-24
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
Question by:JCWEBHOST
    LVL 25

    Expert Comment

    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.
    LVL 142

    Expert Comment

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

    Author Comment

    the rows ISNULL()  must work where parent_id is null and row where the id is not in parent_id
    LVL 142

    Accepted Solution

    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 = ) then url
                       else isnull(url, '~/#') 
         end url
      from yourtable t 

    Open in new window

    LVL 25

    Expert Comment

    select, 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 = m2.parent_id

    Open in new window

    LVL 31

    Expert Comment

    So what is it you want to see as output? Does your example cover all possible criteria?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now