List:General Discussion« Previous MessageNext Message »
From:Mimi Cafe Date:April 15 2010 8:42pm
Subject:Complex Query
View as plain text  
I have three tables from which I need to query for a keyword. The 3 tables
are products, productsubcategories and productcategories. Fron these three
tables, I need to query for a product name or keywork starting with the
products table. if the keyword is not found in the product name or
product_description, then I need to extra any products who parent table
(productsubcategories) name matches the search keyword or any products who
grandparent table (productcategories) category name matches the keyword.

Here is what I thought should work, but it doesn't:

select product_name, product_short_description, product_price from products\
inner join product_subcategories inner join product_categories where
product_name = 'sat nav' or product_name like '%sat nav%'\
or product_short_description  like '%sat nav%' or
product_subcategories.subcategory_name like '%sat nav%'\
or product_categories.category_name like '%sat nav%';

Now I am begin to wonder whether it is possible to retrieve the required row
using a query.


Complex QueryMimi Cafe15 Apr