Column alias not recognized in WHERE-statement

Question

I have a strange 'problem' with one of my created queries. Given the next query:

 SELECT 
 ID,
 DistanceFromUtrecht,
 (
  SELECT
   (MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24))
  FROM
   PricePeriod
  WHERE
   PricePeriod.FK_Accommodation = Accommodation.ID
 ) AS LatestBookableTimestamp
FROM
 Accommodation
WHERE
 LatestBookableTimestamp < UNIX_TIMESTAMP()

phpMyAdmin keeps throwing an error about not having a column named 'LatestBookableTimestamp', even allthough I've a column, retreived by a subquery, that alias. I've also tried it selecting every column with the tableprefix. This didn't work eighter. Finally I've selected all columns by a table-alias and I gave the table an alias. All with no luck.

Can someone tell me what I'm doing wrong? I've even searched for some resources to see if I'm not mistaken, but in many cases authors on the internet use the same syntax as I do.

See updated answer for non-depndedent subquery – Mchl Aug 23 '10 at 18:26 — Mchl, Aug 23 '10 at 18:26

Mchl · Accepted Answer · 2010-08-23T18:25:20.890

11

Use HAVING

HAVING
  LatestBookableTimestamp < UNIX_TIMESTAMP()

On a side note, you're using a dependednt subquery, which is a bad idea performance wise.

Try like this:

SELECT 
  a.ID,
  a.DistanceFromUtrecht,
  pp.LatestBookableTimestamp
FROM
  Accommodation AS a
INNER JOIN (
  SELECT
    FK_Accommodation,
    MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp
  FROM 
    PricePeriod 
  GROUP BY 
    FK_Accommodation
) AS pp    
ON pp.FK_Accommodation = a.ID    
WHERE
  pp.LatestBookableTimestamp < UNIX_TIMESTAMP()

edited Aug 23 '10 at 18:25

answered Aug 23 '10 at 18:20

Mchl

61,444
9
118
120

Thanks for your note; erm, how should I create a non-dependent subquery? – Ben Fransen Aug 23 '10 at 18:22
Thanks a bunch Mchl! I've tried your updated query, this one is almost 4 times faster than when I use HAVING. + 1 + answer! – Ben Fransen Aug 23 '10 at 18:30

score 7 · Answer 2 · answered Aug 23 '10 at 18:19

7

You can't use a column alias in the WHERE clause.
MySQL (and SQL Server) will allow column alias use in the GROUP BY, but it's not widely supported. ORDER BY is the most consistently supported place that supports column alias referencing.

Use:

   SELECT a.id,
          a.distancefromutrecht,
          b.LatestBookableTimestamp
     FROM ACCOMMODATION a
LEFT JOIN (SELECT pp.fk_accommodation,
                  MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp
             FROM PRICEPERIOD pp
         GROUP BY pp.fk_accommodation) b ON b.fk_accommodation = a.id
    WHERE b.LatestBookableTimestamp < UNIX_TIMESTAMP()

answered Aug 23 '10 at 18:19

OMG Ponies

325,700
82
523
502

Thank you, I wasn't aware of that. In the resources I've looked there wasn't a WHERE nor HAVING included in the query so I didn't saw this ;) + 1 – Ben Fransen Aug 23 '10 at 18:25
+1 because I had tried having a column alias in my where clause and it broke my query, and for providing the UNIX_TIMESTAMP() function – Yvonne Aburrow Aug 22 '16 at 14:36

score 1 · Answer 3 · answered Aug 23 '10 at 18:19

1

You would need to take the original query, without the where clause, and turn that into a sub query.

select * from (
 SELECT ID, DistanceFromUtrecht, 
 ( 
  SELECT 
   (MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24)) 
  FROM 
   PricePeriod 
  WHERE 
   PricePeriod.FK_Accommodation = Accommodation.ID 
 ) AS LatestBookableTimestamp 
FROM 
 Accommodation 
) a
WHERE 
 LatestBookableTimestamp < UNIX_TIMESTAMP()

answered Aug 23 '10 at 18:19

Fosco

38,138
7
87
101

By using HAVING instead of WHERE it worked. Is your solution a better one? Or is it better to use HAVING? – Ben Fransen Aug 23 '10 at 18:24
Probably better using HAVING. – Fosco Aug 23 '10 at 18:26

score 1 · Answer 4 · answered Aug 23 '10 at 18:27

1

select a.ID, 
    a.DistanceFromUtrecht, MaxDateUntil - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp 
from (
    SELECT FK_Accommodation, MAX(DateUntil) as MaxDateUntil
    FROM PricePeriod 
    group by FK_Accommodation
) ppm
inner join Accommodation a on ppm.FK_Accommodation = a.ID 
where MaxDateUntil - (ReleaseDays * 60 * 60 * 24) < UNIX_TIMESTAMP()

answered Aug 23 '10 at 18:27

D'Arcy Rittich

167,292
40
290
283

Thanks for your answer. Mchl already provided a working query. + 1 for your time and effort:) – Ben Fransen Aug 23 '10 at 18:31

Column alias not recognized in WHERE-statement

4 Answers4

Linked