Search code examples
mysqlsql-like

SQL Possible syntax error using LIKE

Got SQL Error, believe it is located near the LIKE statement, but not entirely sure why.

SELECT submit_time                                              AS datetime, 
       Max(IF(field_name = 'first-name', field_value, NULL))    AS fName, 
       Max(IF(field_name = 'submission_id', field_value, NULL)) AS id
FROM   wp_cf7dbplugin_submits 
WHERE  form_name = 'Personal Info' 
  AND ( Max(IF(field_name = 'submission_id', field_value, '')) LIKE '%4%' ) 
GROUP  BY submit_time 
ORDER  BY Max(IF(field_name = 'submission_id', field_value, '')) DESC 
LIMIT  0, 40

Are you not able to use LIKE like that? How to rewrite this query?

Database Table snapshot

Please note: submit_time is not always different. submit_time refers to the time that the form was submitted and it holds many other field_name rows besides submission_id. Also field_value is of type varchar, so it's a string despite holding an integer value.

Solution

  • The issue is that you're trying to use a group function in a part of the query that is used before grouping happens. You need to move that condition to a having clause. Also, since you're already performing the MAX(IF(...)) operation in the select, you can reference that field:

    SELECT submit_time AS datetime,
  ...,
  MAX(IF(field_name = 'submission_id', field_value, NULL)) AS id,
  ...
FROM wp_cf7dbplugin_submits
WHERE form_name = 'Personal Info'
GROUP BY submit_time
HAVING id LIKE '%4%'
ORDER BY id DESC LIMIT 0, 40