MySQL 5 Left Joins not working

[bauhsoj]

I recently upgraded from MySQL 4.1 to 5.0.15. However, to my horror I have discovered that no LEFT JOINs work anymore.

For instance, the error #1054 is produced with the message Unknown column 'items.item_id' in 'on clause' when the following left join is executed:

SELECT items.itemid, items.title, md.keywords, cat.category
FROM items_table AS items, meta_data_table AS md, categories_table AS cat
LEFT JOIN restricted_table AS rst ON items.item_id=rst.item_id
WHERE items.title LIKE 'the%'
     AND md.item_id=items.item_id
     AND cat.cat_id=items.cat_id
     AND rst.item_id IS NULL
GROUP BY items.item_id
ORDER BY items.title DESC
LIMIT 10

This query worked fine in MySQL 4.1 and the current 5.0 documentation doesn't seem to indicate that it shouldn't. What could have happened to cause this?

[bauhsoj]

Strangely I have found that removing all references to "meta_data_table" and "categories_table" cause the error Unknown column 'items.item_id' in 'on clause' to stop occurring. How, I haven't a clue, seeing as the error message mentions nothing of these other tables. Additionally, removing those tables from the query is not an option in any case due to the fact that they retrieve essential information.

Anyone have any clues?

Thanks!

[bauhsoj]

In case anyone needs to know, this is on Windows XP SP2, Apache 2.0.54, and PHP 5.0.4 with an AMD 64 processer.

[Kid Charming]

I don't have the experience with 5 to say for sure, but my first guess would be that it's assuming you want to make the LEFT JOIN from the table listed immediately before the JOIN call, which is categories_table. try rearranging your query to put items right before the LEFT JOIN.

[bauhsoj]

Originally Posted by Kid Charming

I don't have the experience with 5 to say for sure, but my first guess would be that it's assuming you want to make the LEFT JOIN from the table listed immediately before the JOIN call, which is categories_table. try rearranging your query to put items right before the LEFT JOIN.

Actually that solved the problem.

I wonder why MySQL 5 would be pickier....

[taagangel]

MySQL 5 is pickier because the new engine tries to match the ANSI SQL standard so it changes the order of how some joins need to be set up.

[guelphdad]

Taagangel

Welcome to Coding Forums. As an FYI the thread you resurected is three years old. Check the posting dates from the left hand side of the posts.

[Nyquest]

I know this thread has been long done and used, but I figured if anyone else is having this problem, I'd share the correct fix.

Kid Charming is correct that the LEFT JOIN is trying to use the table listed in the query, but the fix is actually very simple.

Change:

FROM items_table AS items, meta_data_table AS md, categories_table AS cat

To:

FROM (items_table AS items, meta_data_table AS md, categories_table AS cat)

in all applicable area's. (yes, I just added parenthesis)

Regards,

[guelphdad]

Actually if you are going to correct something use the best solution and that is to get out of the habit of using comma join syntax entirely. Use explicit joins and ON clauses at all times.

FROM 
  items_table AS items
INNER JOIN
  meta_data_table AS md
ON
  md.item_id=items.item_id
INNER JOIN
  categories_table AS cat
ON
  cat.cat_id=items.cat_id
LEFT JOIN 
  restricted_table AS rst 
ON 
  items.item_id=rst.item_id