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.
BTW, my computer is XP Pro with Apache 2.0.59 and ActivePerl 220.127.116.11
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)
GROUP BY product
ORDER BY product ASC
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 --
FROM categories AS c
JOIN electronics AS e
ON e.sid = c.sid
JOIN product_rating AS p
ON p.product = CONCAT_WS(' ', e.manufacturer, e.model)
WHERE c.category = 'Televisions'
BY p.product ASC