title: Counting products associated with hierarchical category listing
body: <p><strong>What I have:</strong>  </p>
  
  <p>A table of categories listed Hierarchically:
  <img src="https://i.stack.imgur.com/2SMUp.png" alt="category table"></p>
  
  <p>A table of products. the column <code>category_id</code> refers to <code>category.id</code>:
  <img src="https://i.stack.imgur.com/kVoyt.png" alt="enter image description here"></p>
  
  <p><strong>What I am trying to do:</strong></p>
  
  <p>I am trying to design a query that will count products of catogries withing specified <code>lft</code> and <code>rgt</code> values.<br>
  for example: between <code>lft</code> = <code>2</code> and <code>rgt</code> = <code>11</code> there are 3 "Immediate subcategories" whose IDs are <code>3</code>, <code>4</code> and <code>5</code>. See that <code>wireless</code> subcategory is not included as it is not an immediate subcategory within the range of <code>lft</code> and <code>rgt</code> but the products of this subcategory will be counted as part of its immediate subcategory which is <code>headphone</code> as you can see from the picture below. <code>Headphone</code> has one direct product and another product relates to its <code>wireless</code> subcategory. </p>
  
  <p>the result should be:  </p>
  
  <p><img src="https://i.stack.imgur.com/ymqtn.png" alt="enter image description here"></p>
  
  <p>I have managed to make a query that retrieves immediate sub-categories within specified <code>lft</code> and <code>rgt</code> values. </p>
  
  <pre><code>    SELECT node.name,node.id, (COUNT(parent.name) - 1) AS depth
      FROM category AS node,
          category AS parent
      WHERE node.lft BETWEEN parent.lft AND parent.rgt
          AND parent.lft BETWEEN {$lft} AND {$rgt}
      GROUP BY node.name
      HAVING depth = 1
      ORDER BY node.lft
  </code></pre>
  
  <p>This query will retrieve the sub-catogries like in the last picture when <code>lft</code> = <code>2</code> and <code>rgt</code> = <code>11</code> but it is missing the count.</p>
  
  <p>I have experimenting to get that table of results right for quite a while and this is what I came up with but unfortunately it does not work well for some reason I do not know. I appreciate any help regarding this.  </p>
  
  <pre><code>            SELECT parent.name, COUNT(product.category_id)
      FROM category AS node1 ,
              product, (
                  SELECT node.name,node.id,node.lft,node.rgt, (COUNT(parent.name) - 1) AS depth
                  FROM category AS node,
                      category AS parent
                  WHERE node.lft BETWEEN parent.lft AND parent.rgt
                      AND parent.lft BETWEEN {$lft} AND {$rgt}
                  GROUP BY node.name
                  HAVING depth = 1
                  ORDER BY node.lft
              ) as parent
      WHERE node1.lft BETWEEN parent.lft AND parent.rgt
              AND node1.id = product.category_id
      GROUP BY parent.lft
      ORDER BY parent.lft
  </code></pre>
  
  <p><strong>UPDATE:</strong> I tried to make SQL fiddle but I got huge list of errors:</p>
  
  <pre><code>    CREATE TABLE category 
          (
           id int auto_increment primary key, 
           name varchar(20), 
           lft varchar(20),
            rgt varchar(20)
          );
  
      INSERT INTO category
      (name, lft,rgt)
      VALUES
      ('ELECTRONICS','1','18'),
      ('TV &amp; audio','2','11'),
      ('Home theater','3','4'),
      ('Blu-ray','5','6'),
      ('Headphone','7','10'),
      ('Wireless','8','9'),
      ('Gaming','12','17'),
      ('Game consoles','13','14'),
      ('Video games','15','16');
  
      CREATE TABLE product 
          (
           id int auto_increment primary key, 
           name varchar(20), 
           category_id varchar(20)
          );
      INSERT INTO product
      (category_id, name)
      VALUES
      ('4','Sony Blu-ray player'),
      ('5','Beats Solo HD'),
      ('6','Beats Solo HD wireless'),
      ('8','PlayStation'),
      ('8','Xbox');       
  </code></pre>