Sagi Bashari wrote:
Michael Stassen wrote:
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'
My apologies. Apparently, I only read the first half of your original message.
However, once I added products.quantity to the SELECT list (before I only selected the id) it worked. Strange.
Of course. Even without seeing your whole message, I should have caught that. It's not strange at all. From the manual page I referenced in my previous message:
The HAVING clause can refer to any column or alias named in a select_expression. It is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)
So, you have to select products.quantity to use it in HAVING.
Thanks, Sagi
I must have been distracted yesterday. Sorry.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
