title: Nested categories counting only one branch with products
body: <pre><code>CREATE TABLE IF NOT EXISTS `site_location` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `level` int(11) NOT NULL,
    `lft` int(11) NOT NULL,
    `rgt` int(11) NOT NULL,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
  
  ///DATA
      INSERT INTO `site_location` (`id`, `level`, `lft`, `rgt`, `name`) VALUES
      (1, 0, 1, 24, 'United Kingdom'),
      (2, 1, 2, 19, 'London'),
      (3, 2, 13, 14, 'Central London'),
      (4, 2, 11, 12, 'East London'),
      (5, 2, 9, 10, 'North London'),
      (6, 2, 7, 8, 'North West London'),
      (7, 2, 5, 6, 'South East London'),
      (8, 2, 3, 4, 'West London'),
      (9, 2, 15, 18, 'South West'),
      (10, 3, 16, 17, 'Battersea'),
      (11, 1, 20, 23, 'Brighton'),
      (12, 2, 21, 22, 'Guildford');
  
  CREATE TABLE IF NOT EXISTS `ads` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `location` int(11) NOT NULL,
    `title` varchar(255) CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY (`id`,`category_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  
  INSERT INTO `ads` (`id`, `category_id`, `location`, `title`) VALUES
  (1, 7, 10, 'Test1'),
  (2, 9, 3, 'Test2'),
  (3, 12, 10, 'Test3'),
  (4, 13, 4, 'Test4');
  </code></pre>
  
  <p>when ID is ID=1 to return this way</p>
  
  <pre><code>[ United Kingdom(4) ]
      London(4)     
      Brighton(0)
  </code></pre>
  
  <p>and when ID is ID = 2</p>
  
  <pre><code>    United Kingdom(4)
        [ London(4) ]
          South West(2)
          Central London(1)
          East London(1)
        Brighton(0)
  </code></pre>
  
  <p>so when is selected "London" I need all its siblings and all its children</p>
  
  <p>the same when is selected "South West" I need all its siblings all its children, its parent all its parent siblings</p>
  
  <pre><code>    United Kingdom(4)
        London(4)
          [ South West(2) ]
            Battersea(2)
          Central London(1)
          East London(1)
        Brighton(0)
  </code></pre>
  
  <p>and as well how many ads have in the location, for example in UK has to have 4 because all ads in the table are 4, south west has to have 2 because in Battersea has 2</p>
  
  <p>when id is 11</p>
  
  <pre><code>United Kingdom(4)
    London(4)
    [ Brighton(0) ]
        Guildford(0)
  </code></pre>
  
  <p>I have a query:
   South West lft:15, rgt:18, level:2</p>
  
  <pre><code> SELECT 
      node.id, 
      node.alias, 
      node.level, 
      node.name, 
      COUNT(ads.id) as ads,
      IF(node.lft = node.rgt-1, "0", "1") AS `has_children`
   FROM 
      site_location AS parent LEFT JOIN
      ads as ads ON (parent.id = ads.location),
      site_location AS node
   WHERE
      parent.lft BETWEEN node.lft AND node.rgt
   AND 
      parent.lft BETWEEN 15 AND 18
   AND 
      node.level &gt; 0
   AND
     node.level &lt; 2 + 3
   GROUP BY node.id
   ORDER BY node.lft
  </code></pre>
  
  <p>this will return </p>
  
  <pre><code> London(4)
    South West(2)
     Battersea(2)
  </code></pre>
  
  <p>I need to be in this way</p>
  
  <pre><code> London(4)
    South West(2)
     Battersea(2)
   Brighton(0) 
  </code></pre>
  
  <p>Is it possible to do this with single query :?
  If anyone can help, It will be great. Thanks in advance!</p>