MySQL Can't Find Field

[rainborick]

My knowledge of MySQL is limited, but a client has a problem with a pre-packaged script that throws an error on a query that looks OK to me.

SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name,
 sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) as total
 FROM orders o,currency_master cm
 INNER JOIN users u ON o.user_id = u.user_id
 INNER JOIN order_status s ON o.order_status = s.order_status_id
 INNER JOIN order_details od
 ON o.order_id = od.order_id
 WHERE od.artist_id = '2' AND o.vorder_currency = cm.vcurrency_code AND o.order_id = '2'
 GROUP BY o.order_id ORDER BY o.order_date DESC

The error is: Unknown column 'o.user_id' in 'on clause'
The field user_id definitely exists in the tables 'order' and 'user'. I've searched a bit and there are some comments that this could be a problem in MySQL 5.?, but I don't know enough to really research this. I'd appreciate any corrections or suggestions.

[Demaestro]

It may be wanting them joined in a more strict order.

Because your last Inner join statement doesn't reference the table you are joining it to directly.... ie the order table, it may not see that column....

It is a stretch but try this if you really are sure the column exists on that table.

I just changed the order so that the joins statements reference each table that they join on.

SELECT o.*, u.user_name, u.email,s.order_status,od.product_discount, cm.nexchange_price,vcurrency_name,
sum((od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) as total
FROM currency_master cm, order_details od
INNER JOIN ON orders o ON o.order_id = od.order_id
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_status s ON o.order_status = s.order_status_id
WHERE od.artist_id = '2' AND o.vorder_currency = cm.vcurrency_code AND o.order_id = '2'
GROUP BY o.order_id ORDER BY o.order_date DESC

[rainborick]

Thanks very much. I'll give it a try.