I've just upgraded from MySQL 4.0.13 to 5.0.41 and all my code works fine except for the SQL statement below. I've determined that the problem is the LEFT JOIN part, i.e. if I remove it the rest of the statement works ok, however I do need to have p.rating in so it's not really an option to leave it out.

I've searched the web and tried all different JOIN combinations but to no avail. Is it possible that someone here could kindly tell me why the statement below doesn't work now under 5.0.41 and what would be the best way to fix it.

Many thanks.

BTW, my computer is XP Pro with Apache 2.0.59 and ActivePerl 5.8.8.82


SELECT DISTINCT(CONCAT_WS(' ', e.manufacturer, e.model)) AS product, c.category, p.rating
FROM electronics AS e
INNER JOIN categories AS c ON c.sid=e.sid
LEFT JOIN product_rating AS p ON p.product=CONCAT_WS(' ', e.manufacturer, e.model)
WHERE c.category='Televisions'
GROUP BY product
ORDER BY product ASC

works? doesn't work?

how does mysql let you know that the query doesn't work? is there a message?

Thank you for your reply.

That's the weird thing, there is no error message at all. MySQL thinks it is working fine but the expected results are not right. I know it isn't working as it should do because if I leave the LEFT JOIN part out - the expected result would for example be 10 records (which would be correct), but with LEFT JOIN in it returns only 3 records (why 3 I don't know) and with the data in the 3 records being all jumbled up.

The table "product_rating" is a seperate table within the database and if the product is the same as in the electronics table then it will output a product rating.

As I said before it works perfectly under 4.0.13 but not for 5.0.41. For some reason the LEFT JOIN part in the statement is not right. Is there another way I could rewrite the SQL statement without using LEFT JOIN, i.e. an internal SELECT statement?

perhaps it's getting confused between the column called product and the expression to which you've assigned a column alias called product

try it like this --please note: DISTINCT is not a function

Problem solved.

I tried writing the statement as you mentioned and it gave the same results. So I went back to my original SQL statement and renamed the "AS product" to "AS product_whatever" and it works fine.

You were correct in that MySQL 5.0 doesn't allow column alias's to be called the same as table column names, where as MySQL 4.0 is more relaxed with the rule.

Thank you very much for your help, it's much appreciated.

yeah, my bad, i got turned around

of course the SELECT, GROUP BY, and ORDER BY clauses should use the CONCAT expression instead of p.product, since p.product is from the right table