Select one record for each category

Databases
1

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
}

products {
id
category_id
}

2

assuming you can get a query to give you what you want, what are you going to do with the results?

according to your query, you have

SELECT c.id as cat_id, p.id as prd_id

that result, by itself, is not very useful

are you planning on running some more queries after this one?

i also assume you have more columns in each of the tables than you have already mentioned

3

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}')
4

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

I hope this makes my question more clear,

thanks again for everyone.

5

probably :slight_smile:

however, you neglected to describe exactly what you wanted

you originally said “fetch one product for each category” but nothing you’ve tried comes close to doing that