TheBlueblib

01-23-2013, 04:20 AM

I have a query that looks something like this:

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > '27'

What I want to accomplish is to have the where clause select rows where 'criteria' is greater than 'math_result', so this is what I would have hoped would work but obviously I get a syntax error "unknown column 'math result'":

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > `math_result`

What I can do is the math operation twice, so this is a current solution, albeit inefficient:

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > ((`column_1` / `column_2`) * `column_3`)

Assuming MySQL doesn't cache the calculation of 'math_result' and it is inefficient, what is the efficient solution I'm looking for? Is a subquery perhaps needed here? Having a separate column to store the 'math_result' data is a solution I've already considered and not fond of if an efficient version of the above code exists.

Much thanks.

UPDATE

So I came across the HAVING clause, and I've tried it and it seems to work in the way I want it to. Is this query a misuse of the expected usage of the HAVING clause and is there some other inefficiency I'm not seeing?

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` HAVING `criteria` > `math_result`

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > '27'

What I want to accomplish is to have the where clause select rows where 'criteria' is greater than 'math_result', so this is what I would have hoped would work but obviously I get a syntax error "unknown column 'math result'":

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > `math_result`

What I can do is the math operation twice, so this is a current solution, albeit inefficient:

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > ((`column_1` / `column_2`) * `column_3`)

Assuming MySQL doesn't cache the calculation of 'math_result' and it is inefficient, what is the efficient solution I'm looking for? Is a subquery perhaps needed here? Having a separate column to store the 'math_result' data is a solution I've already considered and not fond of if an efficient version of the above code exists.

Much thanks.

UPDATE

So I came across the HAVING clause, and I've tried it and it seems to work in the way I want it to. Is this query a misuse of the expected usage of the HAVING clause and is there some other inefficiency I'm not seeing?

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` HAVING `criteria` > `math_result`