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

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

Answer 2

SELECT user_name
FROM
(
SELECT name AS user_name
FROM   users
) AS test
WHERE  user_name = "john"

Answer 3

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

Answer 4

No you cannot. user_name is doesn't exist until return time.

Answer 5

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.

Answer 6

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'