Q:

Top 10 list of products with child products

This is going to be dead easy for any SQL head, but for some reason is alluding me. 
Table product: product_id, parent_id, etc
Table carts_ordered: product_id, quantity, etc

Product children have parent_id set to their parent's product_id.  
This is roughly what I'm doing now:
SELECT c.product_id, p.parent_id, SUM(c.quantity) AS su 
FROM carts_ordered c 
LEFT JOIN product p ON (c.product_id=p.product_id) 
WHERE p.product_id IS NOT NULL 
GROUP BY c.product_id
ORDER BY su DESC LIMIT 0,10

But of course I'm often getting a bunch of child products in the top ten, e.g.:

I would like to merge the sums for all product children to get a top ten list of parent products (with no child products in the top ten list). 
Any help is much appreciated! 

A:

Sine you use WHERE p.parent_id IS NOT NULL, left join can be replaced by inner join instead. 
From the description of problem, it is better to sum the quantity of parent with all their children's, not just the sum of children'. 
SELECT 
    p.parent_id + IF(p.parent_id = 0, p.product_id, 0) as ParentID,
    SUM(c.quantity) AS su 
FROM carts_ordered c 
INNER JOIN product p ON (c.product_id=p.product_id) 
GROUP BY ParentID
ORDER BY su DESC LIMIT 0,10;

