Multilevel Parent Choice / MySQL Question

Question

I probably have a simple question with a MySQL query that has been eluding me lately. I am trying to make a SELECT that counts the number of instances of a data set (orders) and groups these instances by the value that exists in the parent pairs above.

For example:

CREATE TABLE  `so_test`.`categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`product_group` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`products` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_group_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

CREATE TABLE  `so_test`.`orders` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1;

What I want to do is something like:

SELECT count (orders.id), categoryId FROM orders, categories WHERE orders.customer_id in (1,2,3) GROUP BY orders.productId.productGroupId.categoryId

Assuming that there are 17 orders for products in categories 1, 2 orders for products of category 2 and 214 orders for category 3, I hope to return:

count(orders.id), categoryId
============================
      17              1
      2               2
      214             3

If I tried to group, say, product_id, I would be fine .. but the two-level part throws me.

Thanks!

Answer 1

:

select categoryid, count(orders.id)
from category c
left join product_group pg on pg.category_id = c.id
left join products on p on p.product_group_id = pg.id
left join orders o on o.product_id = p.id

count (orders.id) 0, count (*) , .

.