Hi there,

Here's the challenge:

I have an uploads table, containing image filenames, and titles (used for alt text); a catalogue table, containing product ids, descriptions, image ids etc; a categories tables, containing id, name; a sections table, containing id, name; and two lookup tables: cataloguesections, and cataloguecategories.

I need to generate a query which will provide me with one image for each category in a section. The image needs to be from the first product that would appear in that category, ordered by product id.

Here's what I've got so far:
Code:
SELECT `categories`.`name` AS `name`,
	`uploads`.`title` AS `alttext`,
	`uploads`.`filename` AS `image`,
	`bgcolor`,
	`color`,
	`catalogue`.`id` AS `id`
FROM `categories`
INNER JOIN `cataloguecategories` ON `categories`.`id` = `cataloguecategories`.`categoryid`
INNER JOIN `catalogue` ON `catalogue`.`id` = `cataloguecategories`.`catalogueid`
INNER JOIN `cataloguesections` ON `catalogue`.`id` = `cataloguesections`.`catalogueid`
INNER JOIN `sections` ON `sections`.`id` = `cataloguesections`.`sectionid`
LEFT JOIN `uploads` ON `catalogue`.`image` = `uploads`.`id`
WHERE `sections`.`name` = '$section_name'
GROUP BY `categories`.`id`"
This fine, except that the image returned is not necessarily from the first product by product id, since the query does not order the catalogue ids at any point.

I tried adding ORDER BY `catalogue`.`id` at the end of the query, but that throws an error.

I'm sure I need to use a temporary table but I can't quite get my head round it at the moment.

Any ideas?

Many thanks,
M