homepage Welcome to WebmasterWorld Guest from 50.19.172.0
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Left Join query not working in mysql 5.1
knkk

5+ Year Member



 
Msg#: 4121672 posted 3:46 pm on Apr 25, 2010 (gmt 0)

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!

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4121672 posted 6:17 pm on Apr 25, 2010 (gmt 0)

Try prepending the fields with the table name. Also looks like you're missing two commas, one after events.name and one after movies.name. I'd also remove the parentheses in the join (the extra one in the where is okay, but not needed) but it shouldn't be a problem.

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

knkk

5+ Year Member



 
Msg#: 4121672 posted 7:24 am on Apr 26, 2010 (gmt 0)

Thanks, rocknbil. 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!

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4121672 posted 5:09 pm on Apr 26, 2010 (gmt 0)

Just a note,

this query worked:....

AND ((ev.startDate >= '2010-04-26' OR ev.endDate >= '2010-04-26') OR ev.event_type = 'movies')


The way or works is additive, that is, the inner ()'s are not needed "as is." But it's no big deal.

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


I rarely use match, so the foll0wing statement may be false. From what I can gather from the documentation here [dev.mysql.com],

The argument to AGAINST() must be a constant string


and here [dev.mysql.com],

The search string must be a literal string, not a variable or a column name.


it looks to me like you are attempting a regular expression match, "+isb*", and I don't know that it will work. I'd probably change this to use the like operator or regexps.

Another bit of info I found in the second document link:

Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.


You can check that it's using MyISAM tables.

Does the query return an error?

knkk

5+ Year Member



 
Msg#: 4121672 posted 7:08 am on Apr 27, 2010 (gmt 0)

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, rocknbil!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved