1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

Complex? MySQL Query Help

Discussion in 'MySQL' started by Lucky Bastard, Jul 3, 2005.

  1. #1
    Need help with the query detailed below. Will start by trying to explain the table structure and give some dummy data before going in to the half working query.

    Say I have the following tables:

    products_ordered [table]
    *************
    products_id
    tally

    Which is related to:
    products [table]
    ******
    products_id
    products_price
    ....

    and
    products_description [table]
    ******
    products_id
    products_file_name
    ....

    Some example data:
    products_ordered [table]
    *************
    products_id, tally
    123,3
    124,2
    125,1


    Which is related to:
    products [table]
    ******
    products_id, products_price
    123,2.00
    124,25.00
    125,10.00

    and
    products_description [table]
    ******
    products_id, products_file_name
    123,lost_dvd
    124,lost_dvd
    125,other_dvd
    ....

    Note products_id 123, and 124 are both called lost_dvd. The product is basically the same, but they belong to different categories, one (chepest one) is for US delivery, and more expensive one is for foreign delivery.

    Category table structure below:

    products_to_categories [table]
    ******
    products_id
    categories_id

    categories [table]
    *******
    categories_id
    parent_id

    Continuing the above example data:
    products_to_categories [table]
    ******
    products_id,categories_id
    123,10
    124,11
    120,10
    ....

    categories [table]
    *******
    categories_id,parent_id
    10,1
    11,2

    So we can see:
    product 123 (which has sold 3 copies),lost_dvd belongs to Category 10, which is of Category 1 (which happens to be US Delivery)
    product 124 (which has sold 2 copies),lost_dvd belongs to Category 11, which is of Category 2 (which happens to be Foreign Delivery)
    product 125 (which has sold 1 copies),other_dvd belongs to Category 10, which is of Category 1 (which happens to be US Delivery)

    What I want to do is:
    Create a HOT LIST for the different categories (US Delivery, Foreign Delivery) combining the SUM total for the tally across all categories. So when running the hot list for category 1 (USA Delivery), the SQL statement will create the following output:
    products_id, sum_tally
    123,5
    ...

    the hot list for category 2 (Foreign Delivery), the SQL statement will create the following output:
    products_id, sum_tally
    124,5
    125,1
    ...

    As you can see above the sum for lost_dvd US has been added to the sum of lost_DVD Foreign delivery (3+2 = 5).. The other_dvd is only available for Foreign delivery and as such for Foreign has a sum of 1 and US doesn't exist.

    My current SQL statement, which only half works is:
    select sum(po.tally) as 'sum_tally', pd.products_file_name, p.products_id, c.categories_id, c.parent_id from products p, products_description pd, products_ordered po, products_to_categories p2c, categories c where po.products_id = pd.products_id and pd.products_id = p.products_id and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '2' GROUP BY pd.products_file_name ORDER BY sum_tally DESC

    The above is FOR Foreign Delivery as c.parent_id is set to '2'.
    When I run this query for the example output above I get (a snippet of the important fields returned):
    products_id, sum_tally
    124,2
    125,1

    Note the products_id 124 sum_tally is 2. I want it to be 5 (foreign delivery tally of 2 + usa delivery tally of 3), such as:
    products_id, sum_tally
    124,5
    125,1

    When I do the above MySQL select statement for US Delivery (change c.parent_id to '1') it returns (a snippet of the important fields returned):
    products_id, sum_tally
    123,3
    Again, it hasn't added the tally of 2 for the foreign delivery version of this product.
     
    Lucky Bastard, Jul 3, 2005 IP
  2. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I only scanned your post, so forgive me if this is in there somewhere, but what error if any, does the MySQL server kick back when you run the query you posted?
    OK, I just re-read the post. Have you tried (c.parent_id = '2' OR c.parent_id = '1') instead of c.parent_id = '2' Where 2 is foreign and 1 is domestic delivery?

    DISCLAIMER: I'm just throwing ideas out there without really thinking about them :D
     
    exam, Jul 4, 2005 IP
  3. Lucky Bastard

    Lucky Bastard Peon

    Messages:
    406
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks exam, got the solution (I think):


    select pd.products_file_name, sum(po.tally) as sum_tally
    from products_description pd

    join products_description pd2 on
    pd.products_file_name = pd2.products_file_name

    join products_to_categories p2c on
    pd2.products_id = p2c.products_id

    join categories c on
    c.categories_id = p2c.categories_id

    join products_ordered po on
    po.products_id = pd.products_id

    where c.parent_id = 1

    group by pd.products_file_name;
     
    Lucky Bastard, Jul 5, 2005 IP
  4. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Glad you found a solution, sorry I couldn't help :(
     
    exam, Jul 8, 2005 IP