Unknown Column In Where Clause

Question

I have a simple query:

SELECT u_name AS user_name FROM users WHERE user_name = "john";

I get Unknown Column 'user_name' in where clause. Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'?

Answer 1

Unknown column in WHERE clause caused by lines 1 and 2 and resolved by line 3:

1. $sql = "SELECT * FROM users WHERE username =".$userName;
2. $sql = "SELECT * FROM users WHERE username =".$userName."";
3. $sql = "SELECT * FROM users WHERE username ='".$userName."'";

Answer 2

I had the same problem, I found this useful.

mysql_query("SELECT * FROM `users` WHERE `user_name`='$user'");

remember to put $user in ' ' single quotes.

Answer 3

corrected:

SELECT u_name AS user_name FROM users WHERE u_name = 'john';

Answer 4

While you can alias your tables within your query (i.e., "SELECT u.username FROM users u;"), you have to use the actual names of the columns you're referencing. AS only impacts how the fields are returned.

Answer 5

See the following MySQL manual page: http://dev.mysql.com/doc/refman/5.0/en/select.html

"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses."

(...)

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section B.5.4.4, “Problems with Column Aliases”.

Answer 6

SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name has not yet occurred.