Getting total amount of sales for products

[subcool]

MySQL version 5.0.22

 

I have the following tables: "product" and "sales"

 

I put these in pastebin cause they're HUGE.

Product Table SQL: http://pastebin.com/f5ae734b3

Sale Table SQL: http://pastebin.com/f41677401

 

note: i didnt make these tables i just have to work with em

 

Now i need to get the count(sale.id) from all products of 1 developer (each dev has a unique ID and each sale a unique ID) grouped product (1 product can have multiple product_id's)

but here comes the hard part, products have unique ID's (logical) but they also have VERSIONS which have unique ID's too BUT they have 'parentid' set and name is set to NULL

 

example:

product_id = 1

name = Something

 

product_id = 2

name = NULL (empty)

parentid = 1

 

Now i have to count all the sales for ID 1 and 2 together so i get the total amount of sales for that product.

Here is the query that gets the top 25 products of a developer, but this DOESNT add the sales of the childproducts.

 

SELECT sale.product_id, product.name,
Count(sale.id) AS sales
FROM sale Inner Join product ON (sale.product_id = product.id)
WHERE product.developer_id =  '2137'
GROUP BY product.id
ORDER BY sales DESC
LIMIT 0,25

 

If you can/want to help, please do... any questions don't hesitate to ask... i'v been trying this for a few days now without success

[fenway]

How can one product have multiple ids?

[subcool]

How can one product have multiple ids?

 

Because 1 product can have multiple versions, version of the same product have unique ProductID's too but the "name" field is NULL and the ParentID is filled in

 

so if you have

 

ProductID 1, 2 and 3

and ProductID 2 and 3 have "parentid = 1" then they are the same version as ID 1 but their productid is unique. So making it 1 product with multiple ProductID's

its a stupid setup, but i have to work with it

[fenway]

You might as well run a query to get all the product and "related" product ids, then join onto that derived table.

[subcool]

you mean make a temporary table? hmm well i would but we use replication on our servers and temporary tables and replication dont mix =(

[fenway]

No, not temporary... write the query to get all of the related products that you need to query, and then use that "as" a table for the join.

[subcool]

something like this?

 

SELECT values FROM (SELECT * FROM table WHERE devid = '1') WHERE.......etc

 

Cause the main problem is that i am unable to associate the child products with the parents in the query, it counts only counts the parents OR counts the parents and childs seperate. I'v tried it with joining too, i can already get the top 25 Parent products just need to add the # of sales from their childs to the result.

 

This is the query to get the top 25 parent ID's (for a predefined period)

SELECT
countryProductStats.productid AS product_id
FROM
countryProductStats
Inner Join product ON (countryProductStats.productid = product.id)
Inner Join sale ON product.id = sale.product_id
WHERE
sale.sale_date BETWEEN '2007-11-01 0:00:00' AND '2007-11-31 00:00:00' AND
product.name <>  '' AND
product.developer_id = '2137'
GROUP BY
countryProductStats.productid,
ORDER BY
Count(sale.id)  DESC
LIMIT 0,25

[fenway]

Cause the main problem is that i am unable to associate the child products with the parents in the query

Why? is this not where product_id = 43 or parent_id = 43?

[subcool]

Well yes, IF i needed it for 1 single product... but i need it for ALL the developers products and not just one

[fenway]

Then you'd have to join in the products table again.

[subcool]

sorry but im totally lost now... do you have any small examples of queries that do something similair by any chance?

[fenway]

Forget about sales right now... if you join the products table to itself using the parent_id as the join condition, you'll get all parents and children...