我不太精通MySQL查询,通常只做非常简单的JOIN。我正在安装osCommerce,我希望类别页面也包括所有子类别的产品。
select
p.products_image,
pd.products_name,
pd.products_description,
p.products_id,
p.manufacturers_id,
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
products_description pd,
products p
left join
manufacturers m on p.manufacturers_id = m.manufacturers_id
left join
specials s on p.products_id = s.products_id,
products_to_categories p2c
where
p.products_status = '1' and
p.products_id = p2c.products_id and
pd.products_id = p2c.products_id and
pd.language_id = '1' and
(p2c.categories_id = '24' or ###.parent_id = '24')
order by pd.products_name asc
基本上,我也需要将categories表加入到这个查询中,从categories_id=p2c.categories_id的categories表中提取行。然后,我可以引用categories表格中所选行的parent_id列(我会用类似"cat"的东西替换上面的"###")。
然而,对于应该在哪里插入另一个JOIN子句,我对所有左边的联接感到困惑。
任何帮助都将不胜感激。
谢谢!
没有要测试的表,但应该像;
SELECT
p.products_image,
...
FROM products_description pd
JOIN products p
ON pd.products_id = p.products_id
LEFT JOIN manufacturers m
ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s
ON p.products_id = s.products_id
JOIN products_to_categories p2c
ON p2c.products_id = p.products_id
JOIN categories c
ON c.categories_id = p2c.categories_id
WHERE
p.products_status = '1' and
pd.language_id = '1' and
(p2c.categories_id = '24' or c.parent_id = '24')
ORDER BY by pd.products_name ASC