Reputation: 762
I have the following query:
SELECT
`people`.`surename`,
calcWeight(`people`.`surname`,'kiera',6)
as `weight`
FROM
`people`
LEFT JOIN
`dogs`
ON
`people`.`id` = `dogs`.`owner`
ORDER BY
`weight` DESC
The problem is i need to remove all results where weight
, which is a value I'm calculating is 0. I thought it would be WHERE weight
> 0 but apparently weight
doesnt exist till after the where clause. I dont know if i am meant to move where weight
is calculated or move the WHERE.
I dont want to do
WHERE calcWeight(`people`.`surname`,'kiera',6) > 0
because calcWeight takes a long time to compute (when it's happening on thousands of rows) and i dont know if it would cache the result and re-use it.
Any help and advice would be great! Thankyou !
Upvotes: 1
Views: 63
Reputation: 1271241
You best way to do this with a subquery:
select t.*
from (SELECT `people`.`surename`, calcWeight(`people`.`surname`,'kiera',6) as `weight`
FROM `people` LEFT JOIN
`dogs`
ON `people`.`id` = `dogs`.`owner`
) t
where weight > 0
ORDER BY `weight` DESC
Upvotes: 3
Reputation: 169528
Right before your ORDER BY
clause, add HAVING weight > 0
. HAVING
clauses can reference aggregate functions and aliases you've set up in your SELECT
clause.
The difference between WHERE
and HAVING
is that WHERE
is executed before the result set is projected, and HAVING
is executed on the actual projected result set. Therefore, WHERE
has more opportunities to use indexes (I don't think HAVING
filters can use indexes at all), but HAVING
is a bit more flexible. Use WHERE
when possible, and HAVING
otherwise.
Upvotes: 2