pitstop Posted January 20, 2009 Share Posted January 20, 2009 Hi, this code works fine in MSQLv4 but I get an error in MySQL5. I know i have to move the left join to the end of the block but I am getting all goggle eyed trying to get it to work. The manual is not helping me much as I just seem to change the error. I would really appreciate some help and pointers with this. The code that works in MySQLv4 is: $products_query_raw = "select m.manufacturers_name, mrv.rec_v_id, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, pd.products_description from " . TABLE_PRODUCTS . " p, recently_view as rv, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id where p.products_id in (" . $prod_viewed . ") and p.products_status = '1' and p.products_id = rv.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by rv.rec_v_id"; The error I get in MySQLv5 is: 1054 - Unknown column 'p.manufacturers_id' in 'on clause' select count(*) as total from products p, recently_view as rv, products_description pd left join manufacturers m on p.manufacturers_id = m.manufacturers_id where p.products_id in (25003789,25003638,25001258,25000669) and p.products_status = '1' and p.products_id = rv.products_id and p.products_id = pd.products_id and pd.language_id = '1' order by rv.rec_v_id [TEP STOP] I have tried moving the left join to after the first table (table products) without sucess, I suspect I am missing something simple but I just cannot see it. Thank you, Link to comment https://forums.phpfreaks.com/topic/141597-mysql5-left-join-placement/ Share on other sites More sharing options...
fenway Posted January 20, 2009 Share Posted January 20, 2009 You're lucky it worked in mysql 4 -- you got very lucky that comma and JOIN had the same precedence. It's bad to mix them; in fact, NEVER use comma and you'll never go astray. You can cheat: .... from ( " . TABLE_PRODUCTS . " p, recently_view as rv, " . TABLE_PRODUCTS_DESCRIPTION . " pd ) left join " . TABLE_MANUFACTURERS . " m ..... Or, better yet, fix it: $products_query_raw = "select m.manufacturers_name, mrv.rec_v_id, p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, pd.products_description from " . TABLE_PRODUCTS . " p using ( products_id ) inner join recently_view as rv using ( products_id ) inner join " . TABLE_PRODUCTS_DESCRIPTION . " pd using ( products_id ) left join " . TABLE_MANUFACTURERS . " m using ( manufacturers_id ) where p.products_id in (" . $prod_viewed . ") and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by rv.rec_v_id"; Link to comment https://forums.phpfreaks.com/topic/141597-mysql5-left-join-placement/#findComment-741283 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.