Mysql concat and search on multiple tables -

i have 2 tables, products , categories defined this:

table: categories - category_id, category

table: products - product_id, category_id, item_no, description

i'm trying concat description , category single searchable field. when following works, clunky code:

select *, concat(description, ' ',category) searchable  products, categories  concat(description, ' ',category) '%flowers%'  , concat(description, ' ',category) '%rainbow%'  , products.category_id=categories.category_id order products.category_id 

so i'm trying write sub-query this:

select * products, categories,  (select concat(description, ' ',category) searchable  products, categories  products.category_id=categories.category_id  group product_id) sub searchable '%flowers%' , searchable '%rainbow%'  , products.category_id=categories.category_id order products.category_id; 

this query returns multiple incorrect results every product_id, , when try group product_id, every result shows improper searchable field.

any appreciated.

sometimes key simplify. following find products have descriptions or categories matching '%rainbow%'.

by breaking query 2 clauses, we're improving performance eliminating heavy concat operation.

select p.* products p  left join categories c on p.category_id = c.category_id  p.description '%rainbow%'     or c.category '%rainbow%' group p.product_id