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.
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
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;