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

User · Answer

SELECT user name FROM   SELECT name AS user name FROM   users   AS test WHERE  user name    john

User · Answer

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  gt  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   gt  0    You ll still get the alias returned  but now SQL shouldn t bork at the unknown alias

User · Answer

try your task using IN condition or OR condition and also this query is working on spark-1 6 x    SELECT  patient  patient id FROM  patient  WHERE patient IN   User4    User3      or  SELECT  patient  patient id FROM  patient  WHERE patient    User1  OR patient    User2

User · Answer

No you need to select it with correct name  If you gave the table you select from an alias you can use that though

User · Answer

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

User · Answer

No you cannot  user name is doesn t exist until return time

User · Answer

corrected   SELECT u name AS user name FROM users WHERE u name    john

User · Answer

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

User · Answer

What about   SELECT u name AS user name FROM users HAVING user name    john

User · Answer

May be it helps   You can  SET  somevar        SELECT  somevar AS user name FROM users WHERE   somevar     u name      john     It works   BUT MAKE SURE WHAT YOU DO    Indexes are NOT USED here There will be scanned FULL TABLE - you hasn t specified the LIMIT 1 part So  - THIS QUERY WILL BE SLLLOOOOOOW on huge tables    But  may be it helps in some cases

User · Answer

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

User · Answer

Unknown column in WHERE clause caused by lines 1 and 2 and resolved by line 3     sql    SELECT   FROM users WHERE username     userName   sql    SELECT   FROM users WHERE username     userName      sql    SELECT   FROM users WHERE username      userName

User · Answer

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

User · Answer

Just had this problem   Make sure there is no space in the name of the entity in the database   e g    user name  instead of  user name

User · Answer

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

User · Answer

For me the root of the problem was a number which I copied to use in a WHERE clause  The number had  invisible  symbol  at least for MySQL Workbench  I placed the number in the Chrome console it was clearly visible

User · Answer

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

User · Answer

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

[mysql] Unknown Column In Where Clause

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1054