Join Suddenly Failing

--0050450144a6456fda047dc44422
Content-Type: text/plain; charset=ISO-8859-1

I have a website that gets used once a year for a soccer tournament. It has
been working fine since 2006. No script changes since it was last used in
2009. All of a sudden the following script started throwing an error.

SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
teamperson
LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID
LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID
WHERE team.teamID = 22
AND team.TeamID = teamperson.TeamID
AND teamperson.PersonID = person.PersonID
AND person.PeopleTypeID =5
AND contactinfo.ContactTypeID =2

Error: Unknown column 'person.PersonID' in 'on clause' (1054)

There are several of these type scripts and all are giving a similar error.

The server version is 5.0.87. I suspect the hosting company may have
upgraded to a new version of mysql.

Thanks.

Albert

--0050450144a6456fda047dc44422--

RE: Join Suddenly Failing

I ran into this when I upgraded from 4.0.xx to 5.0.xx There was a change in
the logic for the Joins. I determined that the FROM clause needs to be in
parenthesis. i.e. FROM (team, person, teamperson) this allows all of the
fields in all of the tables to be used. The change was made in mysql so
that only the last table (i.e. teamperson) was used for your JOIN

Scott Swaim
I.T. Director
Total Care / Joshua Family Medical Care
(817) 297-4455
Website: www.totalcareclinic.com


NOTICE: The information contained in this e-mail is privileged and
confidential and is intended for the exclusive use of the recipient(s) named
above. If you are not the intended recipient or his or her agent, you are
hereby notified that you have received this document in error and that any
use, disclosure, dissemination, distribution, or copying of this message is
prohibited. If you have received this communication in error, please notify
the sender immediately by e-mail, and delete the original message
-----Original Message-----
From: Albert Padley [mailto:ap3design@gmail.com]
Sent: Friday, January 22, 2010 11:37 AM
To: mysql@lists.mysql.com
Subject: Join Suddenly Failing

I have a website that gets used once a year for a soccer tournament. It has
been working fine since 2006. No script changes since it was last used in
2009. All of a sudden the following script started throwing an error.

SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
teamperson
LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID
LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID
WHERE team.teamID = 22
AND team.TeamID = teamperson.TeamID
AND teamperson.PersonID = person.PersonID
AND person.PeopleTypeID =5
AND contactinfo.ContactTypeID =2

Error: Unknown column 'person.PersonID' in 'on clause' (1054)

There are several of these type scripts and all are giving a similar error.

The server version is 5.0.87. I suspect the hosting company may have
upgraded to a new version of mysql.

Thanks.

Albert


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Join Suddenly Failing

--001636d34353c0ae88047dc46df0
Content-Type: text/plain; charset=ISO-8859-1

Scott,

Thanks. That appears to be the solution.

Albert

On Fri, Jan 22, 2010 at 10:42 AM, Scott Swaim wrote:

> I ran into this when I upgraded from 4.0.xx to 5.0.xx There was a change
> in
> the logic for the Joins. I determined that the FROM clause needs to be in
> parenthesis. i.e. FROM (team, person, teamperson) this allows all of
> the
> fields in all of the tables to be used. The change was made in mysql so
> that only the last table (i.e. teamperson) was used for your JOIN
>
> Scott Swaim
> I.T. Director
> Total Care / Joshua Family Medical Care
> (817) 297-4455
> Website: www.totalcareclinic.com
>
>
> NOTICE: The information contained in this e-mail is privileged and
> confidential and is intended for the exclusive use of the recipient(s)
> named
> above. If you are not the intended recipient or his or her agent, you are
> hereby notified that you have received this document in error and that any
> use, disclosure, dissemination, distribution, or copying of this message is
> prohibited. If you have received this communication in error, please notify
> the sender immediately by e-mail, and delete the original message
> -----Original Message-----
> From: Albert Padley [mailto:ap3design@gmail.com]
> Sent: Friday, January 22, 2010 11:37 AM
> To: mysql@lists.mysql.com
> Subject: Join Suddenly Failing
>
> I have a website that gets used once a year for a soccer tournament. It has
> been working fine since 2006. No script changes since it was last used in
> 2009. All of a sudden the following script started throwing an error.
>
> SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
> teamperson
> LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID
> LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID
> WHERE team.teamID = 22
> AND team.TeamID = teamperson.TeamID
> AND teamperson.PersonID = person.PersonID
> AND person.PeopleTypeID =5
> AND contactinfo.ContactTypeID =2
>
> Error: Unknown column 'person.PersonID' in 'on clause' (1054)
>
> There are several of these type scripts and all are giving a similar error.
>
> The server version is 5.0.87. I suspect the hosting company may have
> upgraded to a new version of mysql.
>
> Thanks.
>
> Albert
>
>

--001636d34353c0ae88047dc46df0--

RE: Join Suddenly Failing

On Fri, January 22, 2010 09:42, Scott Swaim wrote:
> I ran into this when I upgraded from 4.0.xx to 5.0.xx There was a change
> in
> the logic for the Joins. I determined that the FROM clause needs to be in
> parenthesis. i.e. FROM (team, person, teamperson) this allows all of
> the
> fields in all of the tables to be used. The change was made in mysql so
> that only the last table (i.e. teamperson) was used for your JOIN
>
> Scott Swaim
> I.T. Director
> Total Care / Joshua Family Medical Care
> (817) 297-4455
> Website: www.totalcareclinic.com
Actually the change happened half way through the 4.x series. Basically
you need to tell mySql which table you wish to join on. Parentheses is
one way to say check all.
>
> NOTICE: The information contained in this e-mail is privileged and
> confidential and is intended for the exclusive use of the recipient(s)
> named
> above. If you are not the intended recipient or his or her agent, you are
> hereby notified that you have received this document in error and that any
> use, disclosure, dissemination, distribution, or copying of this message
> is
> prohibited. If you have received this communication in error, please
> notify
> the sender immediately by e-mail, and delete the original message
> -----Original Message-----
> From: Albert Padley [mailto:ap3design@gmail.com]
> Sent: Friday, January 22, 2010 11:37 AM
> To: mysql@lists.mysql.com
> Subject: Join Suddenly Failing
>
> I have a website that gets used once a year for a soccer tournament. It
> has
> been working fine since 2006. No script changes since it was last used in
> 2009. All of a sudden the following script started throwing an error.
>
> SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
> teamperson
> LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID
> LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID
> WHERE team.teamID = 22
> AND team.TeamID = teamperson.TeamID
> AND teamperson.PersonID = person.PersonID
> AND person.PeopleTypeID =5
> AND contactinfo.ContactTypeID =2
>
> Error: Unknown column 'person.PersonID' in 'on clause' (1054)
>
> There are several of these type scripts and all are giving a similar
> error.
>
> The server version is 5.0.87. I suspect the hosting company may have
> upgraded to a new version of mysql.
>
> Thanks.
>
> Albert
>
>
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Join Suddenly Failing

--001636d343531fc383047dc47941
Content-Type: text/plain; charset=ISO-8859-1

More precisely, you'll be better off always using explicit joins - that way
you not only prevent this, but also unexpected problems and the need to
review code when something happens to your DB schema.


On Fri, Jan 22, 2010 at 6:42 PM, Scott Swaim wrote:

> I ran into this when I upgraded from 4.0.xx to 5.0.xx There was a change
> in
> the logic for the Joins. I determined that the FROM clause needs to be in
> parenthesis. i.e. FROM (team, person, teamperson) this allows all of
> the
> fields in all of the tables to be used. The change was made in mysql so
> that only the last table (i.e. teamperson) was used for your JOIN
>
> Scott Swaim
> I.T. Director
> Total Care / Joshua Family Medical Care
> (817) 297-4455
> Website: www.totalcareclinic.com
>
>
> NOTICE: The information contained in this e-mail is privileged and
> confidential and is intended for the exclusive use of the recipient(s)
> named
> above. If you are not the intended recipient or his or her agent, you are
> hereby notified that you have received this document in error and that any
> use, disclosure, dissemination, distribution, or copying of this message is
> prohibited. If you have received this communication in error, please notify
> the sender immediately by e-mail, and delete the original message
> -----Original Message-----
> From: Albert Padley [mailto:ap3design@gmail.com]
> Sent: Friday, January 22, 2010 11:37 AM
> To: mysql@lists.mysql.com
> Subject: Join Suddenly Failing
>
> I have a website that gets used once a year for a soccer tournament. It has
> been working fine since 2006. No script changes since it was last used in
> 2009. All of a sudden the following script started throwing an error.
>
> SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
> teamperson
> LEFT JOIN personcontact ON person.PersonID = personcontact.PersonID
> LEFT JOIN contactinfo ON personcontact.ContactID = contactinfo.ContactID
> WHERE team.teamID = 22
> AND team.TeamID = teamperson.TeamID
> AND teamperson.PersonID = person.PersonID
> AND person.PeopleTypeID =5
> AND contactinfo.ContactTypeID =2
>
> Error: Unknown column 'person.PersonID' in 'on clause' (1054)
>
> There are several of these type scripts and all are giving a similar error.
>
> The server version is 5.0.87. I suspect the hosting company may have
> upgraded to a new version of mysql.
>
> Thanks.
>
> Albert
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636d343531fc383047dc47941--

RE: Join Suddenly Failing

>-----Original Message-----
>From: Albert Padley [mailto:ap3design@gmail.com]
>Sent: Friday, January 22, 2010 12:37 PM
>To: mysql@lists.mysql.com
>Subject: Join Suddenly Failing
>
>I have a website that gets used once a year for a soccer tournament. It has
>been working fine since 2006. No script changes since it was last used in
>2009. All of a sudden the following script started throwing an error.
>
>SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,
>teamperson
[JS] Try making those JOINs explicit, I do believe they are treated
differently in recent versions.


Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org