sql recursive menu

Posted on 2012-08-23
Last Modified: 2012-08-23
hey guys i have a table and doing a search from the menu table

here the coloums

id                 parent_ id                  title                 url
1                        0                           Products          
2                        1                           Shoes
3                        2                           Size 5              ~/#

if i enter Shoes to search it must list the child of shoes which is size 5 and url is not null

please help me with the query
Question by:JCWEBHOST
    LVL 142

    Accepted Solution

    this should do:
    ;with data as (
      select id, parent_id, title, url
        from yourtable t
      where t.title = 'Shoes'
      select, t.parent_id, t.title, t.url
        from data
        join yourtable t
          on t.parent_id = 
    select * from data where url is not null

    Open in new window

    LVL 7

    Expert Comment

    Try this query it will help you

    select * from menu
    where title = 'Shoes'
    select * from menu
    where parent_id = (select id from menu
    					where title = 'Shoes')
    	and url IS NOT NULL

    Open in new window


    Author Closing Comment

    works fine

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    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.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    745 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

    13 Experts available now in Live!

    Get 1:1 Help Now