The ERROR 1054 in MySQL occurs because MySQL can't find the column or field you specified in your statement. This error can happen when you execute any valid MySQL statements like a SELECT , INSERT , UPDATE , or ALTER TABLE statement.
By Tech-Assured. January 27, 2021 24959 Views. If you see this error, it means you have forgotten something while creating table.
The MySQL unknown column in field list error happens when you put a column name in your SQL script that can't be found by MySQL. The error above is because there's no student_name column in the students table.
This error message is caused when a column name which you specify in an INSERT statement's field list does not exist in the table into which you are inserting data.
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.
What about:
SELECT u_name AS user_name FROM users HAVING user_name = "john";
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”.
select u_name as user_name from users where u_name = "john";
Think of it like this, your where clause evaluates first, to determine which rows (or joined rows) need to be returned. Once the where clause is executed, the select clause runs for it.
To put it a better way, imagine this:
select distinct(u_name) as user_name from users where u_name = "john";
You can't reference the first half without the second. Where always gets evaluated first, then the select clause.
If you're trying to perform a query like the following (find all the nodes with at least one attachment) where you've used a SELECT statement to create a new field which doesn't actually exist in the database, and try to use the alias for that result you'll run into the same problem:
SELECT nodes.*, (SELECT (COUNT(*) FROM attachments
WHERE attachments.nodeid = nodes.id) AS attachmentcount
FROM nodes
WHERE attachmentcount > 0;
You'll get an error "Unknown column 'attachmentcount' in WHERE clause".
Solution is actually fairly simple - just replace the alias with the statement which produces the alias, eg:
SELECT nodes.*, (SELECT (COUNT(*) FROM attachments
WHERE attachments.nodeid = nodes.id) AS attachmentcount
FROM nodes
WHERE (SELECT (COUNT(*) FROM attachments WHERE attachments.nodeid = nodes.id) > 0;
You'll still get the alias returned, but now SQL shouldn't bork at the unknown alias.
Your defined alias
are not welcomed by the WHERE
clause you have to use the HAVING
clause for this
SELECT u_name AS user_name FROM users HAVING user_name = "john";
OR you can directly use the original column name with the WHERE
SELECT u_name AS user_name FROM users WHERE u_name = "john";
Same as you have the result in user defined alias as a result of subquery or any calculation it will be accessed by the HAVING
clause not by the WHERE
SELECT u_name AS user_name ,
(SELECT last_name FROM users2 WHERE id=users.id) as user_last_name
FROM users WHERE u_name = "john" HAVING user_last_name ='smith'
Either:
SELECT u_name AS user_name
FROM users
WHERE u_name = "john";
or:
SELECT user_name
from
(
SELECT u_name AS user_name
FROM users
)
WHERE u_name = "john";
The latter ought to be the same as the former if the RDBMS supports predicate pushing into the in-line view.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With