Hello, I had an idea to fetch one product for each category,
So I prepared a code to fetch all categories and they children:
SELECT c.id as cat_id
FROM categories c
WHERE (c.parent_id IN (SELECT id FROM categories WHERE parent_id=13))
OR (c.parent_id=13) OR (c.id=13)
After i figured out how to get all child’s and sub-child’s by category id in one query, I added the subquery to fetch products"
SELECT c.id as cat_id, p.id as prd_id
FROM categories c,
(SELECT * FROM products WHERE category_id=c.id AND enabled=1 ORDER BY is_hot DESC LIMIT 1) as p
WHERE (c.parent_id IN (SELECT id FROM categories WHERE parent_id=13))
OR (c.parent_id=13) OR (c.id=13)
but as it turnout I cannot use upper field in subquery:
#1054 - Unknown column 'c.id' in 'where clause'
I know I could use Left join, BUT! I need the left join to fetch the product information by language, this why I need a subquery.
I need to left join the product translation by language:
LEFT JOIN (translate_products tp) ON (tp.product_id=p.id AND tp.lang_id='en')
LEFT JOIN (manufacturers m) ON (m.id=p.manufacturer_id)
is there any possibility to make the query work as I want it?
Simply fetch one product for each category and his child’s ??
the table really simple
categories {
id
parent_id
}
I can post the whole query but don’t see how will it help,
This want i want to run, Get only one product from each category
SELECT
p.id, p.category_id, p.price_sell, p.price_fixed, p.part_num,
p.manufacturer_id, m.title as manuf_title, m.image_path as manuf_image,
p.image_1,tp.title, tp.meta_title, tp.short_description
FROM categories c,
(SELECT * FROM products WHERE category_id=c.id AND enabled=1 ORDER BY is_hot DESC LIMIT 1) as p
LEFT JOIN (translate_products tp) ON (tp.product_id=p.id AND tp.lang_id='{$lang_id}')
LEFT JOIN (manufacturers m) ON (m.id=p.manufacturer_id)
WHERE (c.parent_id IN (SELECT id FROM categories WHERE parent_id='{$toplevel_id}'))
OR (c.parent_id='{$toplevel_id}') OR (c.id='{$toplevel_id}')
Hi, every question I ask here you usually the first and only who delvers the solutions, btw. So thanks.
Anyway I thought I was clear,
I have categories table
id
parent_id
And I have products table
id
category_id
I have many products in different categories, when I select the toplevel category for example 13 where parent_id=0, I want get one product from this category, and one product from each of the sub categories and sub-sub categories.
this query give me the list of all categories id I wanna get the products for:
SELECT c.id as cat_id
FROM categories c
WHERE (c.parent_id IN (SELECT id FROM categories WHERE parent_id=13))
OR (c.parent_id=13) OR (c.id=13)
Now I thought this query will allow me to fetch one product for each selected category:
SELECT c.id as cat_id, p.id as prd_id
FROM categories c,
(SELECT * FROM products WHERE category_id=c.id AND enabled=1 ORDER BY is_hot DESC LIMIT 1) as p
WHERE (c.parent_id IN (SELECT id FROM categories WHERE parent_id=13))
OR (c.parent_id=13) OR (c.id=13)
but this not possible as MYsql throw an error
on this:
category_id=c.id