Use of CONCAT, CONCAT_WS and GROUP_CONCAT

Question

I’m building a query where I look for results within the concatenation and get enough to ask for help. I currently use Mysql with the PHP application.

In the example below, I look for the words "F1000" and "GRID" in a column concatenation of both tables. The product may or may not have a grid.

SELECT p.id, 
       p.id_maker, 
       p.name, 
       p.description, 
       p.specifications 
FROM   product AS p 
WHERE  CONCAT(
           CONCAT_WS(
               ' ', 
               p.name, 
               p.description, 
               p.specifications
           ), ', ', 
           GROUP_CONCAT(
              (
                 SELECT CONCAT_WS(
                     ' ', 
                     g.description, 
                     g.original_cod_maker
                 ) 
                 FROM product_grid AS g 
                 WHERE g.fk_son = p.id
           ) 
           SEPARATOR ', ')
       ) 
       REGEXP '(.*)GRID(.*)F1000(.*)'

This query displays error 1111 (invalid use of group Function) concerning the use of concatenation methods.

I thank you in advance for your help.

Answer 1

The command GROUP_CONCAT serves to concatenate groups when using aggregation functions such as SUM, MAX, AVG, etc..

For your query, you can only search within the fields directly. A query suggestion:

SELECT p.id, 
       p.id_maker, 
       p.name, 
       p.description, 
       p.specifications 
FROM   product AS p 
LEFT JOIN product_grid AS g
    ON (p.id = g.fk_son)
WHERE (
  p.name               LIKE '%GRID%' OR p.name               LIKE '%F1000%'
  OR
  p.description        LIKE '%GRID%' OR p.description        LIKE '%F1000%'
  OR
  p.specifications     LIKE '%GRID%' OR p.specifications     LIKE '%F1000%'
  OR
  g.description        LIKE '%GRID%' OR g.description        LIKE '%F1000%'
  OR
  g.original_cod_maker LIKE '%GRID%' OR g.original_cod_maker LIKE '%F1000%'
)