Reference a MySQL calculated/dynamic column or subquery

I have two tables - leads and brochures. Each sales lead that comes in can request zero or more brochures. I can write the following query to get the number of brochures requested per lead:

SELECT
    id,
    (SELECT COUNT(*) FROM brochures WHERE lead = l.id) AS 'BrochureCount'
FROM leads l

If I want to only show l开发者_开发技巧eads which have requested some brochures, I can repeat the subquery in the WHERE clause:

SELECT
    id,
    (SELECT COUNT(*) FROM brochures WHERE lead = l.id) AS 'BrochureCount'
FROM leads l
WHERE
    (SELECT COUNT(*) FROM brochures WHERE lead = l.id) > 0

But this will presumable run the subquery twice, resulting in an even slower query.

Is it possible to reference BrochureCount in the WHERE clause, either by name, column number or some other format?

Thanks for your time,

Adam

I've found what it's called, and that it can't be done.

It's called a column alias, and apparently it can't be used in a WHERE clause. From the MySQL docs:

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

The leads and how many brochures they ordered, if at least 1:

SELECT
  leads.id,
  COUNT(brochures.lead) AS `BrochureCount`
FROM
  leads
INNER JOIN
  brochures
ON
  leads.id = brochures.lead
GROUP BY
  leads.id

If you wanted all the leads and brochure counts even if they have 0 associated brochures:

SELECT
  leads.id,
  COUNT(brochures.lead) AS `BrochureCount`
FROM
  leads
LEFT OUTER JOIN
  brochures
ON
  leads.id = brochures.lead
GROUP BY
  leads.id

Try the following:

SELECT id, BrochureCount
FROM(
    SELECT id, (SELECT COUNT(*) FROM brochures WHERE lead = l.id) AS 'BrochureCount'
    FROM leads l) data
Where data.BrochureCount > 0

The most straighforward way to reference a calculated column is with a HAVING clause. In general:

<subquery> HAVING <expression>

is equivalent to

SELECT * from (<subquery>) WHERE <expression>