0

I have 2 tables, products and categories defined as this:

Table: categories - category_id, category

Table: products - product_id, category_id, item_no, description

I'm trying to concat the description and category into a single searchable field. When I do the following it works, but is clunky code:

SELECT *, concat(description, ' ',category) as searchable 
FROM products, categories 
where concat(description, ' ',category) like '%flowers%' 
and concat(description, ' ',category) like '%rainbow%' 
and products.category_id=categories.category_id order by products.category_id

So I'm trying to write a sub-query like this:

SELECT *
FROM products, categories, 
(SELECT concat(description, ' ',category) as searchable 
FROM products, categories 
where products.category_id=categories.category_id 
group by product_id) as sub
where searchable like '%flowers%' and searchable like '%rainbow%' 
and products.category_id=categories.category_id order by products.category_id;

This query returns multiple incorrect results for every product_id, and when I try to group by product_id, every result shows the improper searchable field.

Any help would be appreciated.

Cfreak
  • 19,191
  • 6
  • 49
  • 60
AnarFrudan
  • 15
  • 4
  • 1
    Have a look in this answer: http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column – Eduardo M May 15 '13 at 01:00

1 Answers1

0

Sometimes the key is to simplify. The following will find products that have descriptions or categories matching '%rainbow%'.

By breaking up the query into two LIKE clauses, we're also improving performance by eliminating a heavy CONCAT operation.

SELECT p.*
FROM products AS p 
LEFT JOIN categories AS c ON p.category_id = c.category_id 
WHERE p.description LIKE '%rainbow%'
    OR c.category LIKE '%rainbow%'
GROUP BY p.product_id
Steven Moseley
  • 15,871
  • 4
  • 39
  • 50
  • Thanks! That works for getting just the results I needed. I added the second search by doing this: `SELECT p.*, c.* FROM products AS p LEFT JOIN categories AS c ON p.category_id = c.category_id WHERE ( p.description LIKE '%rainbow%' OR c.category LIKE '%rainbow%' ) and ( p.description LIKE '%flowers%' OR c.category LIKE '%flowers%' ) GROUP BY p.product_id;` – AnarFrudan May 15 '13 at 05:08