title: Mysql concat and search on multiple tables
body: <p>I have 2 tables, products and categories defined as this:</p>
  
  <p>Table: categories - category_id, category</p>
  
  <p>Table: products - product_id, category_id, item_no, description</p>
  
  <p>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:</p>
  
  <pre><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
  </code></pre>
  
  <p>So I'm trying to write a sub-query like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>Any help would be appreciated.</p>