Complex? MySQL Query Help

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;

 

Glad you found a solution, sorry I couldn't help