Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unknown column issue with mysql alias

Tags:

mysql

I can't figure out why i am getting an unknown column when the column is an alias that was created. Any help would be great.

code:

SELECT DISTINCT 
    c.id, 
    ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, 
    ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height 
    FROM carpets AS c 
    WHERE c.active = '1' 
    AND (width BETWEEN '0' AND '275') 
    AND (height BETWEEN '0' AND '599') 
    ORDER BY c.item_no 

error:

Unknown column 'width' in 'where clause'

like image 754
Nate Imus Avatar asked Jan 27 '12 16:01

Nate Imus


People also ask

What is column alias in MySQL?

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

Can I use alias in WHERE clause MySQL?

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Can we use alias without AS?

Yes, you can alias without AS.

Can we use column alias in WHERE clause?

In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.


1 Answers

You cannot access the alias directly by name.

One solution is to wrap the query with the aliases in a subquery, and then refer to the alias names in an outer query:

SELECT DISTINCT * 
FROM 
    (
     SELECT c.id, 
          ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, 
          ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height
     FROM carpets AS c 
     WHERE c.active = '1'
    ) sub
WHERE (sub.width BETWEEN '0' AND '275') 
AND   (sub.height BETWEEN '0' AND '599') 
ORDER BY sub.item_no
like image 145
Justin Ethier Avatar answered Oct 16 '22 05:10

Justin Ethier