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 tblrentalswante d 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]

Re: PHP and MySQL question

[color=blue]
> SELECT C.CityID, C.Description, COUNT(R.CityID) AS N FROM tblcities C
> LEFT OUTER JOIN tblrentalswante d 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[/color]
[color=blue]
> The error is: "Unknown column in Where clause 'N' ".[/color]
[color=blue]
> 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?[/color]

Change to:

SELECT C.CityID, C.Description, COUNT(R.CityID) AS N FROM tblcities C
LEFT OUTER JOIN tblrentalswante d 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]

Re: PHP and MySQL question

Marius III wrote:[color=blue]
> SELECT [...] COUNT(R.CityID) AS N [...] WHERE N > 0
> The error is: "Unknown column in Where clause 'N' ".[/color]

Yes, you can't use aliases in where clauses. See
http://dev.mysql.com/doc/refman/4.1/...ith-alias.html
[color=blue]
> BTW: Hope this newsgroup is suitable for my question.[/color]

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