7 messages in com.mysql.lists.mysqlRe: using COUNT in a WHERE
FromSent OnAttachments
Sagi Bashari26 Apr 2004 05:54 
Richard Davey26 Apr 2004 06:16 
Sagi Bashari26 Apr 2004 09:22 
Michael Stassen26 Apr 2004 11:00 
Sagi Bashari26 Apr 2004 11:36 
Egor Egorov27 Apr 2004 06:56 
Michael Stassen27 Apr 2004 10:42 
Subject:Re: using COUNT in a WHERE
From:Egor Egorov (egor@ensita.net)
Date:04/27/2004 06:56:53 AM
List:com.mysql.lists.mysql

Sagi Bashari <sa@boom.org.il> 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'

However, once I added products.quantity to the SELECT list (before I only selected the id) it worked. Strange.

In the HAVING clause you can refer to column or alias that is present in the
select list.