MySQL5 - Left Join Placement

[pitstop]

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,

[fenway]

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";