Trying to add a WHERE Clause to Mysql Query-CodePudding

I'm trying to add a where clause here:

select `submission_id`, 
       max(case when `key` = 'id_usuario' then value end) as user,
       max(case when `key` = 'date_atualizado' then value end) as Data,
       max(case when `key` = 'peso' then value end) as Peso
from wp_e_submissions_values
group by `submission_id`

It is working fine! but I neet to add WHERE 'user' = 1

select `submission_id`, 
       max(case when `key` = 'id_usuario' then value end) as user,
       max(case when `key` = 'date_atualizado' then value end) as Data,
       max(case when `key` = 'peso' then value end) as Peso
from wp_e_submissions_values
WHERE 'user' = 1
group by `submission_id`

I added after FROM ... but it wont work. I'm getting this error:

Attention: #1292 Truncated wrong DOUBLE value: 'user'

Can you guys help me?

CodePudding user response：

WHERE `user` = 1

Press backquote (`) - back tick on keyboard, instead of single quote('), around user.

CodePudding user response：

SELECT * FROM (
select `submission_id`, 
       max(case when `key` = 'id_usuario' then value end) as user,
       max(case when `key` = 'date_atualizado' then value end) as Data,
       max(case when `key` = 'peso' then value end) as Peso
from wp_e_submissions_values
group by `submission_id`
) as mainQuery
WHERE mainQuery.user = 1

You can't use aliases in your WHERE clause. Check: Why Can't I Use Defined Variable in Where Clause

Also, when you type: user you will see it turn blue. This is a keyword. Check: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

That's why I always use my table variable name field like so:

SELECT * FROM (
select submissionValues.submission_id, 
       max(submissionValues.id_usuario) as user,
       max(submissionValues.date_atualizado) as Data,
       max(submissionValues.peso) as Peso
from wp_e_submissions_values submissionValues
group by submissionValues.submission_id
) as mainQuery
WHERE mainQuery.user = 1

CodePudding user response：

Upon reading your question more closely, it looks like the assertion on user should be happening in a HAVING clause rather than a WHERE clause.

SELECT
    submission_id,
    MAX(CASE WHEN `key` = 'id_usuario' THEN value END) AS user,
    MAX(CASE WHEN `key` = 'date_atualizado' THEN value END) AS Data,
    MAX(CASE WHEN `key` = 'peso' THEN value END) AS Peso
FROM wp_e_submissions_values
GROUP BY submission_id
HAVING user = 1;