Chud37 Chud37 - 4 months ago 10
SQL Question

WHERE clause in SQL failing

I have the following SQL:

SELECT `id`,`postcode`,
(SELECT COUNT(*) FROM `kids` WHERE `kids`.`bookingID` = `booking`.`id`) AS `k`,
(SELECT COUNT(*) FROM `youth` WHERE `youth`.`bookingID` = `booking`.`id`) AS `y`
FROM `booking`
WHERE `k` > 0 AND `y` > 0


However it fails because of unknown column
k
. Although its not a real column in the database, I have defined it in the
SELECT
, So where am I going wrong?

Answer

You cannot reference aliases defined in the SELECT in the WHERE clause. This is true of SQL in general, not only MySQL.

MySQL offers an extension. In this case, you can use the HAVING clause for this purpose:

SELECT `id`,`postcode`,
       (SELECT COUNT(*) FROM `kids` WHERE `kids`.`bookingID` = `booking`.`id`) AS `k`,
       (SELECT COUNT(*) FROM `youth` WHERE `youth`.`bookingID` = `booking`.`id`) AS `y`
FROM `booking`
HAVING `k` > 0 AND `y` > 0;