将一个表连接到一个对我来说太复杂的SQL查询

Question

I'm not all that well-versed in MySQL querying, and I usually only do very simple JOINs. I am working with an installation of osCommerce, and I want the category page to include products from all subcategories as well.

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

Basically, I need to join the categories table to this query as well, pulling the row from the categories table where categories_id = p2c.categories_id. Then, I can reference the parent_id column from the selected row from the categories table (I would replace the "###" above with something like "cat").

However, I'm getting confused with all the left joins as to where I should insert another JOIN clause.

Any help would be much appreciated.

Thanks!

Answer 1

Don't have the tables to test against, but should be something as simple as;

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