No, you can't use an alias in the WHERE clause. Even if you could, you'd have a problem here. The WHERE clause is used to decide which rows to look at in advance of any calculations. You're asking mysql to count the rows which match the WHERE clause, but you're also asking it to determine the WHERE match according to the count. See the problem? That's why you can't use COUNT in the WHERE clause. Fortunately, we have the HAVING clause for this purpose. What you need is
SELECT products.id FROM products, orders WHERE orders.product_id = products.id GROUP BY products.id HAVING COUNT(orders.id) >= products.quantity
Tried that, see my original message - didn't really work: ERROR 1054: Unknown column 'products.quantity' in 'having clause'
However, once I added products.quantity to the SELECT list (before I only selected the id) it worked. Strange.
Thanks, Sagi
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
