CODEWITHSUNDEEP
mysql
N8imus
N8imus

Reputation: 253

Unknown column issue with mysql alias

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'

Upvotes: 10

Views: 11785

Answers (3)

Justin Ethier
Justin Ethier

Reputation: 134167

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

Upvotes: 14

Mithrandir
Mithrandir

Reputation: 25337

You can use aliases in the order by clause, but you can't use aliases in the where or group by clause. Either you repeat the expression or you can use a subquery.

Upvotes: 9

Brian
Brian

Reputation: 6450

I don't think you can use your "width" alias in your "width between .. and .."; you need to repeat the raw calculation, sadly. Same for "height". So, the following should work:

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      (((SUM(c.width_feet)*12)+(SUM(c.width_inches))) BETWEEN '0' AND '275') 
AND      (((SUM(c.height_feet)*12)+(SUM(c.height_inches))) BETWEEN '0' AND '599') 
ORDER BY c.item_no

Upvotes: 0

Related Questions