GROUP BY and ORDER BY

[mickyginger]

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:

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

[r937]

i don't understand your tables

each section has multiple catalogues, and each catalogue has multiple products?? how is this relationship implemented? i don't see a product tabler or product ids being used anywhere

then, each catalogue has multiple categories? so even if we find one image per catalogue, how does the image relate to the category?

perhaps you could do a SHOW CREATE TABLE for each table to clear up some of this...

[mickyginger]

It would probably help if I gave some context. The database holds data about educational resources.

There are 12 categories in total: Books and DVDs, Listening Resources, Vocabulary Games... etc.
There are 2 sections: Key Stages 1 and 2, Key Stages 3 and 4.

Each product can belong to many categories, and many stages.

'Selective Mutism' for example is a book which is appropriate for both sections, and resides in two categories.

When a user is browsing the online shop, they first land on a page where they can select between sections (Key Stages), then they can decide which category they want to browse. Key Stages 1 and 2 > Books and DVDs will contain some different products to Key Stages 3 and 4 > Books and DVDs, and some that are the same.

At the moment the following query will do a fairly good job:

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` = '$category'
GROUP BY `categories`.`id`

The problem is the order of the categories needs to be preserved, so that disrupts the order of the catalogue ids.

You can see the site live here: www.languageforlearning.co.uk/shop
http://www.languageforlearning.co.uk...Stages-1-and-2 will show you what I'm on about.

Check out
http://www.languageforlearning.co.uk...ning-Resources
The first item should be 'Active Listening Cards' (product code AL1), but it appears second.

I hope that makes sense!

M

[r937]

perhaps you could do a SHOW CREATE TABLE for each table to clear up some of this...

[mickyginger]

Here you go.. Thanks r937.

//catalogue
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| prefix           | varchar(4)   | NO   | PRI | NULL    |       |
| suffix           | varchar(4)   | NO   | PRI | NULL    |       |
| id               | varchar(6)   | YES  | MUL | NULL    |       |
| image            | varchar(255) | YES  |     | NULL    |       |
| title            | varchar(255) | NO   |     | NULL    |       |
| description      | text         | YES  |     | NULL    |       |
| price            | decimal(5,2) | YES  |     | NULL    |       |
| VAT              | tinyint(4)   | YES  |     | NULL    |       |
| related_products | varchar(255) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

//sections
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| image | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

//cataloguesections
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| catalogueid | varchar(5) | NO   | PRI | NULL    |       |
| sectionid   | int(1)     | NO   | PRI | NULL    |       |
+-------------+------------+------+-----+---------+-------+

//categories
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(2)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | YES  |     | NULL    |                |
| bgcolor | varchar(7)   | NO   |     | #ffffff |                |
| color   | varchar(7)   | NO   |     | #000000 |                |
+---------+--------------+------+-----+---------+----------------+

//cataloguecategories
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| catalogueid | varchar(5) | NO   | PRI | NULL    |       |
| categoryid  | int(1)     | NO   | PRI | NULL    |       |
+-------------+------------+------+-----+---------+-------+

//uploads
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| filename      | varchar(255) | NO   |     | NULL    |                |
| filesize      | int(11)      | NO   |     | NULL    |                |
| dims          | varchar(255) | NO   |     | NULL    |                |
| type          | varchar(3)   | NO   |     | NULL    |                |
| title         | varchar(255) | NO   |     | NULL    |                |
| authorityid   | int(1)       | NO   |     | NULL    |                |
| catalogue     | tinyint(1)   | NO   |     | 0       |                |
| catalogue_img | tinyint(1)   | NO   |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+

[r937]

i hate to be a real stickler, but i wanted the result of the SHOW CREATE TABLE statements

SHOW CREATE TABLE will produce, as you might guess, the CREATE TABLE statement for the table

if i had the CREATE TABLE statements, i could run them into my local testing mysql database, and easily build a query from those tables using my front end interface utility

as it is, i would have to read off your table layouts and re-type everything, and today i'm just too lazy to do that

[mickyginger]

No need to apologise... I really should stop trying to be clever... It clearly hasn't got me very far

Here you go:

CREATE TABLE `catalogue` (
  `prefix` varchar(4) NOT NULL,
  `suffix` varchar(4) NOT NULL,
  `id` varchar(6) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `price` decimal(5,2) DEFAULT NULL,
  `VAT` tinyint(4) DEFAULT NULL,
  `related_products` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`prefix`,`suffix`),
  KEY `id_index` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE `categories` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `bgcolor` varchar(7) NOT NULL DEFAULT '#ffffff',
  `color` varchar(7) NOT NULL DEFAULT '#000000',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

CREATE TABLE `cataloguecategories` (
  `catalogueid` varchar(5) NOT NULL,
  `categoryid` int(1) NOT NULL,
  PRIMARY KEY (`catalogueid`,`categoryid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `sections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

CREATE TABLE `cataloguesections` (
  `catalogueid` varchar(5) NOT NULL,
  `sectionid` int(1) NOT NULL,
  PRIMARY KEY (`catalogueid`,`sectionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `filesize` int(11) NOT NULL,
  `dims` varchar(255) NOT NULL,
  `type` varchar(3) NOT NULL,
  `title` varchar(255) NOT NULL,
  `authorityid` int(1) NOT NULL,
  `catalogue` tinyint(1) NOT NULL DEFAULT '0',
  `catalogue_img` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8

Many thanks!

[r937]

Check out
http://www.languageforlearning.co.uk...ning-Resources
The first item should be 'Active Listening Cards' (product code AL1), but it appears second.

so Resources for Foundation Stage and Key Stages 1 and 2 is the section, and Listening Resources is the catalogue

where do the categories come in?

so each of those boxes contains (with its own add-to-cart button) a product, but i don't see a products table anywhere

is that what the uploads table is for?

if so, i don't see how the uploads are related to the catalogue

in the catalogue table, what is the related_products column for? what kinds of values are in there?

in the uploads table, what is the catalogue column for?

also, you mentioned product code AL1, but i don't see a product code column anywhere

finally, why did you feel you needed a GROUP BY clause here? you're not aggregating anything

[mickyginger]

I've actually updated the site using a workaround in PHP, so the site behaves as it should right now, but I'd rather do this with MySQL if I can.

Resources for Foundation Stage and Key Stages 1 and 2 is the section, Listening Resources is the category.

Catalogue contains all the products.

Uploads contains all the images and pdfs uploaded by the user. The catalogue has a row `image` which stores the `uploads`.`id`, so the upload (image in this case), can be associated with the product.

Related products is a string of product codes separated by a comma, something like: BK1,BK2,BK11,UWM4 etc. I use that to generate a query for those products from the `catalogue` table when a product, or the 'Find out more' link is clicked on. For example: http://languageforlearning.co.uk/sho...-Resources/AL1

The product code is stored in the table as `id`... It should probably rename that `product_code` or something similar...

The query, because of the amount of many-to-many relationships there are will bring back multiple results for each category. I just need one result for each category: `categories`.`name`, `categories`.`bgcolor`, `categories`.`color`, `uploads`.`filename` AS `image`, `uploads`.`title` AS `text`, WHERE `uploads`.`filename` is the image associated with the first product in that section and that category sorted alphabetically by product code (`catalogue`.`id`). That's why I added the GROUP BY clause.

Hope that makes sense...

[r937]

Hope that makes sense...

yes... now

don't change any of the column names, as you are now used to them

i don't see the many-to-many problem yet, but give me time

you said "Resources for Foundation Stage and Key Stages 1 and 2 is the section, Listening Resources is the category" and you also said "I just need one result for each category" and yet when i look at that sample page i see a whole bunch of catalogue products down the page...

so which is it?

[mickyginger]

http://languageforlearning.co.uk/sho...Stages-1-and-2 displays the categories. I need an image for each category. There are 12 categories, so I need a result set of 12 rows, each containing the filename of the image associated with the first product that would appear that category, when ordered alphabetically by product code (`catalogue`.`id`).

However, there are also 2 sections. 'Foundation Stage and Key Stages 1 and 2', and 'Key Stages 3 and 4'... The images will be different for each category pages, since the products in the first section will not (necessarily) be in the second section.

[r937]

Resources for Foundation Stage and Key Stages 1 and 2 is the section, Listening Resources is the category.

whoa, i thought i understood, but i don't

there is no relationship between sections and categories in the tables

[mickyginger]

No direct relationship between categories and sections.

Hmm... Perhaps it would make more sense to look at it from the perspective of a product.

So lets take 'Identification, Assessment, Strategies & Resources' (http://languageforlearning.co.uk/sho...s-and-DVDs/BK2)

It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that category, but it is also suitable for Key stages 3 and 4, so it is associated with that category as well. So it appears 4 times throughout the shop.

Since both sections contain all 12 categories, there's no real need for them to have a relationship in the db, but they need different images when the categories are displayed, depending on which section the customer is browsing at the time. I associate the product BK8 to the categories and sections via look up tables (`cataloguecategories` and `cataloguesections`).

[mickyginger]

It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that category, but it is also suitable for Key stages 3 and 4, so it is associated with that category as well. So it appears 4 times throughout the shop.

That should read:
It belongs in the Books and DVDs category, but it also belongs in the Assessments category. It is suitable for Foundation Stages and Key Stages 1 and 2, so it is associated with that section, but it is also suitable for Key stages 3 and 4, so it is associated with that section as well. So it appears 4 times throughout the shop.

Sorry bout that... its confusing enough as it is!

M

[r937]

i am now thoroughly confused, and i think you should consider a slight redesign of your tables


there is no relationship between sections and categories, and i think you need to build that table

then, cataloguecategories makes sense (it would make more sense if named categorycatalogues, but that's a quibble), so that the products can be tied to the correct category in a section

catalogue sections makes no sense, unless you want to tie products to a section without those products belonging to a category in that section, which i doubt, so you can delete it

only after these changes would i attempot to write your queries