CODEWITHSUNDEEP
mysqlsql
kjones1876
kjones1876

Reputation: 762

Confused at how to structure my SQL query

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

cdhowie
cdhowie

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

Related Questions