Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Left Join not working after moving to 5.1

    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:

    http://www.dbforums.com/mysql/165584...sql-5-1-a.html

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    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!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2010
    Posts
    3
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •