jbh — 2011-01-06T08:20:49-05:00 — #1
Strange. I'm curious, what is it about this query that trips up mysql 5? I use:
- Server version: 5.0.91-community
I notice there were bugs in earlier versions. Maybe my host just for this test server needs to update mysql 5?
SELECT [B]music_albums.*[/B],users.username,inventory.stock,inventory.upc_code, DATE_FORMAT(release_date,'%M %d, %Y') AS rel FROM music_albums,users LEFT JOIN inventory ON ([B]inventory.album_id=music_albums.id[/B]) WHERE users.uid=music_albums.artist_id AND music_albums.id='8'
This has worked perfectly on mysql 4. Now, with mysql 5+, it gives this error:
#1054 - Unknown column 'music_albums.id' in 'on clause'
How does it not recognize the column music_albums.id ? What do I have to do differently, from now on, for mysql 5? I am testing our site on php 5/mysql 5 to make sure our site won't break before we update our servers.
PS. Edit: I see it was a feature change and am reading the mysql docs but I think I need coffee to understand it...but for those going through the same thing, look at this page:
jbh — 2011-01-06T08:52:12-05:00 — #2
Fixed it with a lame query (for now);
SELECT music_albums.*,users.username,inventory.stock,inventory.upc_code, DATE_FORMAT(release_date,'%M %d, %Y') AS rel FROM music_albums,users,inventory WHERE users.uid=music_albums.artist_id AND music_albums.id='8' limit 1
I hope this helps others who are stuck.
guelphdad — 2011-01-06T11:37:25-05:00 — #3
get out of the habit of using comma join syntax. stick specifically with INNER and OUTER joins. The problem you encounter is mysql 5 being more conforming with sql standards.
the comma takes precedence over the LEFT (OUTER) join and thus it can't find the column inventory.album_id because it is only looking at music_albums and users and trying to perform that join first.
DATE_FORMAT(release_date,'%M %d, %Y') AS rel
LEFT JOIN inventory
jbh — 2011-01-06T14:09:19-05:00 — #4
Thank you for this. I have to re-wire my brain for mysql 5. It should be a tremendous learning experience.