Using CONCAT, CONCAT_WS, and GROUP_CONCAT

Question

Using CONCAT, CONCAT_WS, and GROUP_CONCAT

Navigation

#1 by (1 votes)

4

I'm building a query where I search for results within the concatenation and got 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 concatenation of columns 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 presents an error 1111 (invalid use of group function) regarding the use of concatenation methods.

Thank you in advance for your help.

php mysql query

asked by anonymous 06.01.2017 / 12:52

1 answer

Password validation saved in SQLite C # - Problems reading data from an XML returned from a site

score 1 · Answer 1

The GROUP_CONCAT command is used to concatenate groups when using aggregate 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%'
)