According to the standard, the comma
operator has lower precedence than any of the JOIN variants.
Thus the query:
select * from t1, t2 join t3 on a=b;
actually means:
select * from t1, (t2 join t3 on a=b);
Since (according to ANSI SQL) column names in an ON
condition are resolved against the join operands (t2, t3 in
this case), then the first query above must produce a
name resolution error, because there is no column 'a' in
tables t2, t3.
On the other hand the query:
select * from (t1, t2) join t3 on a=b;
means that the whole cross-product (t1, t2) is the left
join operand. Consequently column 'a' in the ON
condition can be resolved against the table (t1, t2).
All this is described in the latest 5.0 manual:
http://dev.mysql.com/doc/refman/5.0/en/join.html
therefore:
catalog/wishlist.php around line 50
replace $products_query with the following
$products_query = tep_db_query("
SELECT pd.products_id,
pd.products_name,
pd.products_description,
p.products_image,
p.products_status,
p.products_price,
p.products_tax_class_id,
IF(s.status, s.specials_new_products_price, NULL)
AS specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price)
AS final_price
FROM (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd)
LEFT JOIN " . TABLE_SPECIALS . " s
ON (p.products_id = s.products_id)
WHERE pd.products_id = '" . $product_id . "' AND p.products_id = pd.products_id AND pd.language_id = '" . $languages_id . "' order by products_name");
and in file catalog/includes/boxes/wishlist.php
near line 45 replace $products_query with the following
$products_query = tep_db_query("
SELECT pd.products_id,
pd.products_name,
pd.products_description,
p.products_image,
p.products_price,
p.products_tax_class_id,
IF(s.status, s.specials_new_products_price, NULL)
AS specials_new_products_price,
IF(s.status, s.specials_new_products_price, p.products_price)
AS final_price from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd)
LEFT JOIN " . TABLE_SPECIALS . " s
ON (p.products_id = s.products_id)
WHERE pd.products_id = '" . $wishlist_id . "' AND p.products_id = pd.products_id AND pd.language_id = '" . $languages_id . "' order by products_name");
See this in action at OBAZAAR or download at SEOPerfectCart
