Paul Hollands: May 2007 Archives

I've had some problems porting SQL which works fine on MySQL 4+ to version 5. It is more picky about your left join syntax and any SQL you have which uses them will break if moved to 5 unless you do the folowing:

The column you want to do your first LEFT JOIN on needs to be LAST in your FROM statement list:

FROM diary_entry_type det, diary_user_entry due, user u,
diary_location dl, diary_entry_status des, diary_entry_status_type
dest, diary_entry d
LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id =
d.diary_entry_id AND deto.diary_entry_type_id = 3)

Will work.

FROM  diary_entry d, diary_entry_type det, diary_user_entry due, user
u, diary_location dl, diary_entry_status des, diary_entry_status_type
dest
LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id =
d.diary_entry_id AND deto.diary_entry_type_id = 3)

Will not. However you can also use brackets thus:

FROM  (diary_entry d, diary_entry_type det, diary_user_entry due, user
u, diary_location dl, diary_entry_status des, diary_entry_status_type
dest)
LEFT JOIN diary_entry deto ON(deto.master_diary_entry_id =
d.diary_entry_id AND deto.diary_entry_type_id = 3)

Posted by pj at 12:59 PM | Comments (0)