Link to home
Start Free TrialLog in
Avatar of JCWEBHOST
JCWEBHOST

asked on

query search

Hey guys, I have to do a search through a menu table a products table
Here is my menu table
Id        int
parent_id  int
title   varchar(50)
url   varchar(MAX)

Here is my product table
Id  int
menu_id   int
p_name   varchar(50)

Now my goal is that I need a query to seach for key words in my two tables to display my products to my web page

My problem is the my menu with sub category I need to search menu with sub categories
Have a look at my website with products
http://www.gasworks.jc-web-sites.co.za/
Avatar of awking00
awking00
Flag of United States of America image

My firewall prevents me from looking at the website. Can you post some sample data for the two tables and what you want as results?
SOLUTION
Avatar of Tony303
Tony303
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JCWEBHOST
JCWEBHOST

ASKER

this is my menu table
id   int
parent_id   int
title   varchar
url     varchar


this is my product table

id   int
menu_id  int
p_name   varchar

i have more that one sub catergory

so i think the best way to search for some thing like this exsample

catergory one
         
            catergory two

                                catergory     three
                   
                                              product

i could have my product in any sub catergory

so what i was thinking if this was possirable?

to list all the category and sub category with product name

like

category  1     category  2          category  3          product name

so my search is done correctly
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you can have max 3 levels

SELECT m1.id,m1.Title as cat1 ,
 m2.id,m2.Title as cat2 ,
 m3.id,m3.Title as cat3 ,
p.*
FROM  Menu m as m1  
left JOIN Menu m as m2 on m1.id = m2.parent_id
left JOIN Menu m as m3 on m2.id = m3.parent_id 
inner JOIN  Product p ON  p.menu_id = coalesce(m3.id,m2.id,m1.id) 
WHERE m1.title LIKE '%YOUR KEY WORD%'
OR isnull(m2.title,'') LIKE '%YOUR KEY WORD%'
OR isnull(m3.title,'') LIKE '%YOUR KEY WORD%'
OR p.p_name LIKE  '%YOUR KEY WORD%'

Open in new window

More levels possible look at a CTE-solution to build your hierarchy
http://blogs.msdn.com/b/simonince/archive/2007/10/17/hierarchies-with-common-table-expressions.aspx
is it possirable to return the product id? from the products table?
Did you run the code? Then you would see that it is returned with all of the columns of products table (p.*)
they is an incorrect syntax where 'as'
Change

FROM  Menu m as m1  
left JOIN Menu m as m2 on m1.id = m2.parent_id
left JOIN Menu m as m3 on m2.id = m3.parent_id


To

FROM  Menu as m1  
left JOIN Menu as m2 on m1.id = m2.parent_id
left JOIN Menu as m3 on m2.id = m3.parent_id
Indeed the initial tablealias I left by adding the different alias for ech level.  So leaving out the m  as  tony's  corrected will do.