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:
SELECT `categories`.`name` AS `name`,
`uploads`.`title` AS `alttext`,
`uploads`.`filename` AS `image`,
`catalogue`.`id` AS `id`
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
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.