Column attribute not recognized in WHERE clause

Question

I have a strange “problem” with one of my generated queries. Given the following 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 continues to throw an error saying that it does not have a column named "LatestBookableTimestamp", even if I have a column received by a subquery, this alias. I also tried to select each column using tableprefix. This does not work fighter. Finally, I selected all the columns using the table alias, and I gave the table an alias. All without luck.

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

Answer 1

Use HAVING

 HAVING LatestBookableTimestamp < UNIX_TIMESTAMP() 

On a side note, you use a dependent subquery, which is a poor representation of the idea.

Try it 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() 

Answer 2

You cannot use a column alias in a WHERE clause.
MySQL (and SQL Server) will allow the use of column aliases in GROUP BY, but it is not widely supported. ORDER BY is the most consistently maintained place supporting column references.

Using:

  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() 

Answer 3

You will need to take the original request without the where clause and turn it into an additional request.

 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() 

Answer 4

 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()