MySQL multi-level parent selection/join question

Question

Hi,

I have a hopefully simple MySQL query question which is eluding me at late at night. I'm trying to do a SELECT which counts the number of instances of a set of data (orders) and groups those instances by a value which exists in a parent a couple levels above the order itself.

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'm looking to do is something in the neighborhood of:

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

Assuming there are 17 orders for products in category 1, 2 orders for products in category 2, and 214 orders for category 3, what I'm hoping to get back is:

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

If I was trying to group by say product_id I'd be fine..but the two-levels-up portion is throwing me.

Thanks!

Answer 1

Just join them together:

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

For categories without an order, count(orders.id) will return 0, while count(*) would return one or more, depending on the number of productgroups and products.

An inner join would not count categories without orders at all.