Left Join not working after moving to 5.1

knkk · #1 (**permalink**) 04-25-10, 12:14

Please look at this:

Code:

WARNING [1024] Error while executing query
select ev.event_type, ev.EventId, ev.VenueId, ev.Price, ev.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 ev.VenueId = '493' and ((ev.startDate >= '2010-04-25' or ev.endDate >= '2010-04-25') or ev.event_type = 'movies') order by moviename asc, ev.startDate asc
1054:Unknown column 'ev.EventId' in 'on clause'

This query was working perfectly fine in 4.1, but as you can see, is throwing up errors in 5.1.

I saw this thread:

LEFT JOIN syntax error on upgrading to MySQL 5.1

but it did not make too much sense to me. Can someone kindly help? Thank you for your time.

Pat Phelan · #2 (**permalink**) 04-25-10, 12:53

I don't have a copy of MySQL handy at the moment to test this, but I'm 99% certain that the 5.1 syntax is a bit closer to ISO standard than 4.1 was. If so, the highlighted comma needs to be removed:

Code:

select ev.event_type, ev.EventId, ev.VenueId
,  ev.Price, ev.Schedule, ev.startDate
,  ev.endDate, events.name eventname, movies.name moviename
   from eventvenue ev
,  events left join (movies)  -- leading comma is probably your problem
      on (movies.ID = ev.EventId)
   where  events.ID = ev.EventId
      and ev.VenueId = '493'
      and ((ev.startDate >= '2010-04-25' or ev.endDate >= '2010-04-25')
         or ev.event_type = 'movies')
   order by moviename asc, ev.startDate asc

-PatP

knkk · #3 (**permalink**) 04-26-10, 03:32

Thanks, PatP. So this query worked:

Code:

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:

Code:

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!

Pat Phelan · #4 (**permalink**) 04-26-10, 04:44

Without any error message, I'm going to assume that the quotation marks need to be changed to apostrophe's to make the syntax 5.x compliant.

-PatP

knkk · #5 (**permalink**) 04-27-10, 03:10

My problem was actually that I'd set ft_min_word_len to 3 earlier, and when I migrated, it became the default 4. I reset it to 3 and recreated the indexes (indices?) and the query started working normally (throwing up results, as opposed to 0 results earlier).

Thanks, PatP!

Internet Services

Web Development

Digital Marketing

Consumer Tech