Trying to add a WHERE Clause to Mysql Query

Question

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?

Use `WHERE user = 1` _without_ the single quotes. This is a typo error. — Tim Biegeleisen, Aug 19 '22 at 13:53
Doens't work, I'm getting: Attention: #1292 Truncated wrong DOUBLE value: 'user' — Michael Bouix, Aug 19 '22 at 13:56
You're all looking at it wrong, you can't use aliases in WHERE clauses guys. — Swedo, Aug 19 '22 at 14:03

score 0 · Accepted Answer · answered Aug 19 '22 at 14:27

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;

Trying to add a WHERE Clause to Mysql Query

1 Answers1