SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Possibly need to construct a very complex query

    Hi all,

    I am producing a sales analyser function for a client so that he can monitor online sales. My client is able to view sales by highest quantity sold per month, and highest revenue per month.

    By what I also want my client to do, is to be able to view the sales of Items within a specified Category. The catch is that in my database I have a hierachy of categories (around 4). So I have a table called categories, where each categories has a parent id which links into another category in the same table. The highest level category is 'Root'. Following that I have 4 main categories, and then they have sub-categories, and then they also have sub-categories. I then have a products table where each product will contain a foreign key value to the lowest level category it resides it (in PHP I can use a recursive function to get to the parent category).

    So, if my client wishes to view all sales for a given month and for a specified top level category, how exactly to I implement a query that uses some form of recursion to get to that category that has been selected?

    Thanks

    Tryst
    Last edited by Tryst; Mar 1, 2005 at 13:18.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you don't need a category called root, the 4 main categories should each have a parent of null

    how many levels of actual categories are there, and do all 4 main categories go down the exact same number of levels before getting to a level with products, or can products be tied to (sub)categories at any level (which is the more general and flexible design)?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    There are 3 levels of categories (not including root) meaning that alll products go down the same level of categories. for example...

    Fine Art Materials->Accessories->Adhesvies & Tapes

    Would be the 3 categories where 'Fine Art Materials' is the Top most, and 'Adhesvies & Tapes' is lowest category (products would be related to this category - and all other categories at the same level).

    At this moment in time products cannot be tiered to sub categories.

    (my local version of MySQL is 3.23, while my hosting company has version 4.*)

    Thanks

    Tryst

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    are there any products tied to the first or second levels? what if a particular 2nd level subcategory doesn't have any third level subsubcategories?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, its structered so that there is a fixed level of catgories (3), and products CAN ONLY be assigned to a third level category.

    Thanks

    Tryst

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    assuming the tables and columns are called --

    categories ( id, name, parentid )
    products ( id, name, catid, salesdate, salesamt )
    Code:
    select sum(products.salesamt) as totals_sales
      from categories as top
    inner
      join categories as sub
        on top.id = sub.parentid
    inner
      join categories as subsub
        on sub.id = subsub.parentid
    left outer
      join products
        on subsub.id = products.catid      
     where top.id = 937
       and products.salesdate >= '2005-02-01'
       and products.salesdate <  '2005-03-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow! WWorking it out in my head, that should work.

    I'll have to wait until I get home though, before I can try it out.

    Top stuff, r937!

    Will keep you posted.

    Tryst

  8. #8
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ouch! I ran this query (Albiet, a bit modified) and it kill my computer, twice!

    My CPU was on 100% until I had to reboot my laptop.

    The modified query i ran, is as follows:

    Code:
    SELECT itm.prod_code, prd.name, SUM(itm.quantity * prd.price) AS Total
    FROM categories AS top
    INNER JOIN categories AS sub ON (top.category_id = sub.parent_id)
    INNER JOIN categories AS subsub (sub.category_id = subsub.parent_id)
    LEFT OUTER JOIN products AS prd ON (subsub.category_id = prd.category_id)
    INNER JOIN order_items AS itm ON (prd.code = itm.prod_code)
    INNER JOIN orders AS ord ON (itm.order_id = ord.order_id)
    WHERE (top.category_id = 2)
    AND (MONTH(ord.date) = '12')
    AND (DATE(ord.date) = '2004')
    GROUP BY prod_code
    ORDER BY Total DESC
    LIMIT 20
    To me, I can't see anything wrong. I 'think' all the joins are correct. But for some reason this is killing my computer

    Tryst

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    change the LEFT OUTER JOIN to INNER JOIN (i didn't realize you wanted to list individual items)

    move the two AND conditions for order fields to their appropriate ON clause, just in front of the WHERE clause, and change them so that they test a range of values (applying a function to a field means an index cannot be used on it)

    make sure all the columns mentioned in any of the ON clauses, other than those that are already declared as PRIMARY KEYS, have an index on them
    Code:
    SELECT itm.prod_code
         , prd.name
         , SUM(itm.quantity 
             * prd.price)   AS Total
      FROM categories AS top
    INNER JOIN categories AS sub 
        ON top.category_id = sub.parent_id
    INNER JOIN categories AS subsub 
        ON sub.category_id = subsub.parent_id
    INNER JOIN products AS prd 
        ON subsub.category_id = prd.category_id
    INNER JOIN order_items AS itm 
        ON prd.code = itm.prod_code
    INNER JOIN orders AS ord 
        ON itm.order_id = ord.order_id
       AND ord.date >= '2004-12-01'
       AND ord.date < '2005-01-01'
     WHERE top.category_id = 2
    GROUP 
        BY itm.prod_code
         , prd.name
    ORDER BY Total DESC
    LIMIT 20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awesome!! That has done the trick

    Thanks, Rudy!

    (I don't think any of the Foriegn keys in my tables have indexes on them, can I just apply an INDEX to these Foriegn key columns without affecting the data in the db?)

    Tryst

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, just use the ALTER TABLE ADD INDEX syntax

    you realize that indexes will have to be updated whenever you insert/delete rows, so those operations will be a bit slower (if at all noticeable) but selects will be much faster (noticeably so)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    just out of curosity, why was my initial Query in an endless loop, and was your initial query meant to determine the total number of sales for a given category?

    Thanks

    Tryst

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, out all day

    Quote Originally Posted by Tryst
    just out of curosity, why was my initial Query in an endless loop, and was your initial query meant to determine the total number of sales for a given category?
    i'm sure it would have ended eventually, and yes, guess which category -- the one with id = 937
    rudy.ca | @rudydotca
    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
  •