sql recursive menu

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
JCWEBHOSTAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
;with data as (
  select id, parent_id, title, url
    from yourtable t
  where t.title = 'Shoes'
 UNION ALL
  select t.id, t.parent_id, t.title, t.url
    from data
    join yourtable t
      on t.parent_id = data.id 
)
select * from data where url is not null

Open in new window

0
 
aplusexpertCommented:
Try this query it will help you

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

Open in new window


Thanks...
0
 
JCWEBHOSTAuthor Commented:
works fine
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.