Left Join query not working in mysql 5.1

[knkk]

Please see this:

WARNING [1024] Error while executing query:
select event_type, EventId, VenueId, Price, Schedule, ev.startDate, ev.endDate, events.name eventname, movies.name moviename from `eventvenue` ev, `events` left join (movies) on (movies.ID = ev.EventId) where events.ID = ev.EventId and VenueId = '493' and ((ev.startDate >= '2010-04-25' or ev.endDate >= '2010-04-25') or event_type = 'movies') order by moviename asc, ev.startDate asc
1054:Unknown column 'ev.EventId' in 'on clause'

This query was working fine in mysql 4.1, but not in mysql 5.1. Can anyone please give me any pointers? Thank you for your time!

[Atli]

Hey.

Did anything else change, besides the MySQL version? Like the OS it is being run on?
Could you show us the definitions of the tables involved?

I would advice you to create an alias for all long table names and to include them on all columns when you are using joins, just to avoid misunderstandin gs and name conflict errors.

And you shouldn't quote numeric values, like IDs. It just causes MySQL to have to convert them before using them, thus wasting resources. (And it can possibly open you up to security vulnerabilities .)

[knkk]

Thanks, Atli. So this query worked:

SELECT ev.event_type
     , ev.EventId
     , ev.VenueId
     , ev.Price
     , ev.Schedule
     , ev.startDate
     , ev.endDate
     , e.name eventname
     , m.name moviename 
  FROM eventvenue ev
  JOIN events e
    ON e.ID = ev.EventId
  LEFT 
  JOIN movies m 
    ON m.ID = ev.EventId 
 WHERE ev.VenueId = 493 
   AND ((ev.startDate >= '2010-04-26' OR ev.endDate >= '2010-04-26') OR ev.event_type = 'movies') 
 ORDER 
    BY moviename ASC
     , ev.startDate ASC

However, I'm having a problem with another query now, that was working in4.1 but is not working in 5.1:

SELECT * FROM locations WHERE MATCH (Name, StreetAddress1, StreetAddress2, Area, City, Pin, Reviewer, CategoryKeywords, ProfileText, UserNames, UserComments) AGAINST ("+isb*" IN BOOLEAN MODE) limit 1

Will greatly appreciate any pointers!