0

I tried searching for a similar case here in SO but I can't find the right search result. My problem is this:

I have this query which works fine and displays a lot of results (net_salary is the result of subtraction between salary and totaldeductions fields which are both stored as float. I can't remember why but I needed to store them as float):

SELECT name, address, city, state, zip_code, salary-totaldeductions as net_salary FROM employees WHERE state = 'CA' ORDER BY address ASC

If I try to modify it and add AND net_salary > 5000 the page/frame becomes blank:

SELECT name, address, city, state, zip_code, salary-totaldeductions as net_salary FROM employees WHERE state = 'CA' AND net_salary > 5000 ORDER BY address ASC

I tried searching where the code fails, it seems if (mysqli_stmt_prepare($stmt, $MyQuery)) fails. The first query works and net_salaryworks but on the second query where I tried using the variable net_salary in a condition it fails, is it wrong to use the variable that was created in a query as a condition in a query? Can you spot what was wrong?

Improve this question
4
  • 2
    Try ---> AND (salary - totaldeductions) > 5000 Commented Sep 12, 2016 at 13:45
  • 1
    The where clause evaluates the condition first and at that time, the AS net_salary is an unknown. Try the one suggested by @TheOneandOnlyChemistryBlob Commented Sep 12, 2016 at 13:46
  • type decimal (instead of float) would have worked also since monetary values will always have precisely 2 numerals after decimal point Commented Sep 12, 2016 at 13:47
  • Thanks guys, so I guess the summarized answer is you can't use an alias in a WHERE? I had to use alias so I can sort the columns of the table (Some columns aren't columns in MySQL Database but were created after some calculations between existing columns). Commented Sep 12, 2016 at 17:08

2 Answers 2

Reset to default
1

To get the result try below query : 

SELECT name, address, city, state, zip_code, (salary - totaldeductions) as net_salary FROM employees WHERE state = 'CA' AND (salary - totaldeductions) > 5000 ORDER BY address ASC

Here you need to put same condition in where conditions. If you want to use alias into where than it will not work but Having is used but Having is used with aggregate column.

Try this query.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I used salary - totaldeductions as net_salary with no clause as there's only 1 step of calculation there though, but this made it clear for me that my mistake was using Alias in a WHERE statement in MySQL Query
0

Because net_salary is an alias I think you need to use HAVING instead of WHERE:

SELECT name, address, city, state, zip_code, salary-totaldeductions as net_salary FROM employees WHERE state = 'CA' HAVING net_salary > 5000 ORDER BY address ASC

In future, if a SQL call fails, you can look at $stmt->error to see what went wrong.

Improve this answer

5 Comments

HAVING refers to GROUP BY or aggregates not aliases
@TheOneandOnlyChemistryBlob Actualy -> "MySQL extends this behavior to permit the use of an alias in the HAVING clause for the aggregated column" . You are correct about most RDBMS, but not MySQL .
HAVING can also refer to aliases.
Just to sharpen this statement - Only in MySQL @BenHillier
@sagi I believe you. The problem is that I've used MySQL and MariaDB exclusively since 2001. I don't know what's standard and whats implementation specific anymore ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.