CODEWITHSUNDEEP
mysql
user3113782
user3113782

Reputation: 77

Mysql alias column not found in where condition

I alias a formula in a new column by the name of 'radial', I have no idea why I faced with this error "Unknown column 'radial' in 'where clause'".

select `id`,(sqrt(pow(`x`,2)+pow(`y`,2))) AS `radial` FROM `wdata` where `fieldtype` = 3 and `occupied` = 0 AND `x`>0 AND `y`<0 AND `radial`>$minafstand AND `radial`<$maxafstand ORDER BY rand()

Upvotes: 2

Views: 2347

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

That is correct. You cannot use column aliases in the where clause. MySQL has an extension where you can put them in having instead:

select `id`,(sqrt(pow(`x`,2)+pow(`y`,2))) AS `radial`
FROM `wdata`
where `fieldtype` = 3 and `occupied` = 0 AND `x`>0 AND `y`<0
having `radial`>$minafstand AND `radial` < $maxafstand
ORDER BY rand();

This is quite specific to MySQL, and works as expected when the query is not doing aggregation. Otherwise, you would need to use a subquery.

Upvotes: 5

Related Questions