PHP and MySQL question

[Marius III]

Hi there,

I am used to program with Delphi etc...

I get an error on the following query:

SELECT C.CityID, C.Description, COUNT(R.CityID) AS N FROM tblcities C
LEFT OUTER JOIN tblrentalswanted R ON C.CityID = R.CityID LEFT OUTER JOIN
tblprovinces P ON C.ProvinceID = P.ProvinceID WHERE N > 0 GROUP BY C.CityID
ORDER BY C.Description

The error is: "Unknown column in Where clause 'N' ".

It is self explanatory but why can I not do this.
What is the alternative to select all fields where aggregated field is
greater than certain condition?
Thanks alot!

BTW: Hope this newsgroup is suitable for my question.

[Steve]

SELECT C.CityID, C.Description, COUNT(R.CityID) AS N FROM tblcities C
LEFT OUTER JOIN tblrentalswanted R ON C.CityID = R.CityID LEFT OUTER JOIN
tblprovinces P ON C.ProvinceID = P.ProvinceID WHERE N > 0 GROUP BY C.CityID
ORDER BY C.Description The error is: "Unknown column in Where clause 'N' ". It is self explanatory but why can I not do this.
What is the alternative to select all fields where aggregated field is
greater than certain condition?

Change to:

SELECT C.CityID, C.Description, COUNT(R.CityID) AS N FROM tblcities C
LEFT OUTER JOIN tblrentalswanted R ON C.CityID = R.CityID
LEFT OUTER JOIN tblprovinces P ON C.ProvinceID = P.ProvinceID
WHERE COUNT(R.CityID) > 0 GROUP BY C.CityID
ORDER BY C.Description
---
Steve

[Ewoud Dronkert]

Marius III wrote:

SELECT [...] COUNT(R.CityID) AS N [...] WHERE N > 0
The error is: "Unknown column in Where clause 'N' ".
Yes, you can't use aliases in where clauses. See
http://dev.mysql.com/doc/refman/4.1/...ith-alias.html
BTW: Hope this newsgroup is suitable for my question.

It isn't, really. Your put PHP in the subject but the question has nothing
to do with it. Try comp.databases.mysql in the future.

--
E. Dronkert