List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:January 22 2010 5:51pm
Subject:Re: Join Suddenly Failing
View as plain text  
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 <scott@stripped> 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@stripped]
> Sent: Friday, January 22, 2010 11:37 AM
> To: mysql@stripped
> 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=1
>
>


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

Thread
Join Suddenly FailingAlbert Padley22 Jan
  • RE: Join Suddenly FailingScott Swaim22 Jan
    • Re: Join Suddenly FailingAlbert Padley22 Jan
    • RE: Join Suddenly FailingWm Mussatto22 Jan
    • Re: Join Suddenly FailingJohan De Meersman22 Jan
  • RE: Join Suddenly FailingJerry Schwartz22 Jan