Ben Ben - 1 year ago 111
MySQL Question

MySQL WHERE end as column name 'unknown'

I have the following MySQL query.

p.`id` as id,
p.`postinguser` as postinguser,
p.`page` as page,
p.`likedby` as likedby,
p.`redwabled` as redwabled,
case r.`redwabledby`
when '5' then r.`time`
else p.`time`
end as date_time
FROM `uc_posts` p left join `uc_redwables` r on = r.dwable
WHERE (p.`postinguser` = '5' OR find_in_set('5',p.`redwabled`)) AND (p.`time` < '2016-08-19 12:31:12') order by date_time desc LIMIT 20

This collates information from two different tables and returns the following batch of results:

enter image description here

My problem is I want to change the
query line to:

WHERE (p.`postinguser` = '5' OR find_in_set('5',p.`redwabled`)) AND (date_time < '2016-08-19 12:31:12') order by date_time desc LIMIT 20

So that it selects only the finalised collated rows with a
2016-08-19 12:31:12
. This would exclude
id: 316
in this example (which has a p.time under but it's r.time is higher). However right now I just get an 'unknown column' error message, presumably because the column doesn't actually exist.

Could someone find a way of doing this?

For reference this is an image of the other table:

enter image description here


Answer Source

The reason you are getting this error is that date_time is an alias, and MySQL evaluates the WHERE clause before this alias may exist.

You have two options here. You can either move the CASE expression into the WHERE clause:

WHERE (p.postinguser = '5' OR find_in_set('5',p.redwabled)) AND
      CASE r.redwabledby WHEN '5'
                         THEN r.time < '2016-08-19 12:31:12'
                         ELSE p.time < '2016-08-19 12:31:12'

Or, you can use the alias date_time as is directly in a HAVING clause. That is, use the following WHERE and HAVING clauses:

WHERE (p.postinguser = '5' OR find_in_set('5',p.redwabled))
HAVING date_time < '2016-08-19 12:31:12'

The reason you can use date_time in a HAVING clause is that this clause is evaluated after aliases are computed. Typically, HAVING applies a filter to each group in an aggregation query. But, since you did use GROUP BY, the HAVING clause will apply to every record.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download