Mysql problem

Report · Nov 02, 2006

I have a problem with this select:

SELECT Place,TN1nn, TN1vn, Points, places.City, places.Year,
clubs.Clubname, countries.countryname
FROM champ_women, places, countries LEFT JOIN clubs on
clubs.ClubID=champ_women.ClubID
WHERE champ_women.CityID=places.CityID AND
champ_women.CountryID=countries.CountryID

Mysql reports "Unknown column 'champ_women.ClubID' in 'on clause'"

That column exists definitely. The select works online without any
problem but fails locally (MySQL-Client-Version: 5.0.22). Has it to do
with different mysql-versions?

Martin

Report · Nov 02, 2006

Martin Lang wrote:
> Mysql reports "Unknown column 'champ_women.ClubID' in 'on clause'"
>
> That column exists definitely. The select works online without any
> problem but fails locally (MySQL-Client-Version: 5.0.22). Has it to do
> with different mysql-versions?

Yes. The way joins work was altered in MySQL 5.0.12 to bring it into
line with the SQL standard. The situation is explained on the following
page (you have to scroll down a long way to find it):

http://dev.mysql.com/doc/refman/5.0/en/join.html

It's quite complicated, but I think the following should work:

SELECT Place,TN1nn, TN1vn, Points, places.City, places.Year,
clubs.Clubname, countries.countryname
FROM places, countries, champ_women LEFT JOIN clubs on
clubs.ClubID=champ_women.ClubID
WHERE champ_women.CityID=places.CityID AND
champ_women.CountryID=countries.CountryID

Alternatively, try this:

SELECT Place,TN1nn, TN1vn, Points, places.City, places.Year,
clubs.Clubname, countries.countryname
FROM (champ_women, places, countries) LEFT JOIN clubs on
(clubs.ClubID=champ_women.ClubID)
WHERE champ_women.CityID=places.CityID AND
champ_women.CountryID=countries.CountryID

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Report · Nov 02, 2006

David Powers wrote:
> Yes. The way joins work was altered in MySQL 5.0.12 to bring it into
> line with the SQL standard. The situation is explained on the following
> page (you have to scroll down a long way to find it):
>
> http://dev.mysql.com/doc/refman/5.0/en/join.html

Oh yes, that is quite complicated, at least for me. I will have to study
about. But your suggestion did the job - thank you very much for that!

Martin

Report · Nov 02, 2006

.oO(David Powers)

>Yes. The way joins work was altered in MySQL 5.0.12 to bring it into
>line with the SQL standard. The situation is explained on the following
>page (you have to scroll down a long way to find it):
>
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>
>It's quite complicated, but I think the following should work:

Looks like it's just a change in operator precedence (besides some other
changes, which don't matter here). But it's good to know (I'm still on
MySQL 4.1.x on my local server).

>SELECT Place,TN1nn, TN1vn, Points, places.City, places.Year,
>clubs.Clubname, countries.countryname
>FROM places, countries, champ_women LEFT JOIN clubs on
>clubs.ClubID=champ_women.ClubID
>WHERE champ_women.CityID=places.CityID AND
>champ_women.CountryID=countries.CountryID

What about replacing the WHERE constraints with INNER JOINs? Should fix
the problem as well.

Micha

Report · Nov 02, 2006

Michael Fesser wrote:
> Looks like it's just a change in operator precedence

It is, but the MySQL documentation is more like a German philosophical
treatise than a straightforward explanation of the change.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/