« Sending plain text and HTML version in the same email | Main | Styling Safari buttons »
May 10, 2007
LEFT JOINS and MySQL 5+
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)
Tags: MySQL
Posted by pj at May 10, 2007 12:59 PM