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