SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY and ORDER BY

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    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` = '$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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    perhaps you could do a SHOW CREATE TABLE for each table to clear up some of this...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Here you go.. Thanks r937.

    Code:
    //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       |                |
    +---------------+--------------+------+-----+---------+----------------+

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No need to apologise... I really should stop trying to be clever... It clearly hasn't got me very far

    Here you go:
    Code:
    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!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mickyginger View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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...

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mickyginger View Post
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by mickyginger View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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`).

  14. #14
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •