MySQL question

[jonny75904]

Technically this belongs in a MySQL forum but because it is zen cart related I'm hoping to find answers here....

I load my cart using an easy populate feed. More recently I have begun incorporating automated feeds.

Easy populate does not load images for categories. I am attempting to write a query that will select ONE image from one product in each category.

Here is what I have so far:

SELECT categories_id, products_image
from zen_products join zen_products_to_categories on
zen_products.products_id = zen_products_to_categories.products_id
order by categories_id

The problem is it selects all images for each category. How can I make it select one image per category?

[lat9]

Try adding " limit 1" to the end of your SQL query.

[gjh42]

A more portable form that will work on any server/db would be

SELECT categories_id, products_image
from ' . TABLE_PRODUCTS . ' p join ' . TABLE_PRODUCTS_TO_CATEGORIES . ' p2c 
on p.products_id = p2c.products_id
order by categories_id limit 1

[jonny75904]

Sorry, that's not quite what I'm looking for - that only returns one category and one image. I need all categories, but only one product image per category. Sorry if I wasn't more clear on that.

[gjh42]

I had a feeling the "limit 1" would not do just what you wanted. I'm not good enough with MySQL yet to say offhand how to do it.

[gjh42]

There was a thread a couple/few months ago where a very similar topic was discussed - picking a random product image from each category to display as that category's thumbnail.