title: List of Top brands in any category or subcategory
body: <p>I am using SQL Server 2008, I urgently need sql query or stored procedure to display top brands in any category or sub-category. For example if I pass IdCategory=2, the result set should display top brands in Electronics by performing products count on idCategory=2 as well as its sub categories and sub-sub-categories. If I pass IdCategory=38, the result should display top brands in Phones &amp; Mobile Devices as well as Mobile Phones. I hope I am able to clear my requirement. </p>
  
  <p>Here are the tables in my database.</p>
  
  <p><strong>Categories</strong></p>
  
  <pre><code>IdCategory    CategoryName              ParentCategoryId
  ---------------------------------------------------------
  1             Appliances                Null
  2             Electronics               Null
  38            Phones &amp; Mobile Devices   2
  39            Cameras &amp; Photography     2
  115           Mobile Phones             38
  121           Digital Cameras           39
  </code></pre>
  
  <p><strong>Brands</strong> </p>
  
  <pre><code>IdBrand       BrandName 
  ------------------------
  1             Nokia
  2             Samsung
  3             Canon
  </code></pre>
  
  <p>Following table breaks many to many relation between Categories and Brands tables</p>
  
  <p><strong>CategoriesBrands</strong> </p>
  
  <pre><code>IdCategoriesBrand    IdCategory   IdBrand
  -----------------------------------------
  1                    2            1
  2                    38           1
  3                    115          1
  4                    2            2
  5                    38           2
  6                    115          2
  </code></pre>
  
  <p><strong>Products</strong> </p>
  
  <pre><code>IdProduct     Product Name       IdCategory    IdBrand
  ---------------------------------------------------------
  1             AAAA               115           1
  2             BBBB               115           2
  3             CCCC               121           3
  4             DDDD               115           1
  5             EEEE               121           3
  </code></pre>
  
  <p>Assumption about products table</p>
  
  <ul>
  <li>product can be added in only third level sub-sub-category (e.g Mobile Phones, Digital Camerass)</li>
  </ul>
  
  <p>Here are scripts to create tables </p>
  
  <pre><code>CREATE TABLE [dbo].[Categories](
      [IdCategory] [bigint]  NOT NULL,
      [CategoryName] [nvarchar](50) NULL,  
      [ParentCategoryId] [bigint] NULL
      CONSTRAINT [PK_Categories_IdCategory] PRIMARY KEY CLUSTERED (   [IdCategory] ASC )
  ) ON [PRIMARY]
  
  GO
  
  
  CREATE TABLE [dbo].[Brands](
      [IdBrand] [bigint]  NOT NULL,
      [BrandName] [nvarchar](50) NULL
      CONSTRAINT [PK_Brands_IdBrand] PRIMARY KEY CLUSTERED (  [IdBrand] ASC )
  ) ON [PRIMARY]
  
  GO
  
  CREATE TABLE [dbo].[CategoriesBrands](
      [IdCategoriesBrand] [bigint]  NOT NULL,
      [IdCategory] [bigint] NULL,
      [IdBrand] [bigint] NULL,
      CONSTRAINT [PK_CategoriesBrands] PRIMARY KEY CLUSTERED (    [IdCategoriesBrand] ASC )
  ) ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[CategoriesBrands]  WITH CHECK ADD  CONSTRAINT [FK_CategoriesBrands_Brands] FOREIGN KEY([IdBrand])
  REFERENCES [dbo].[Brands] ([IdBrand])
  GO
  
  ALTER TABLE [dbo].[CategoriesBrands] CHECK CONSTRAINT [FK_CategoriesBrands_Brands]
  GO
  
  ALTER TABLE [dbo].[CategoriesBrands]  WITH CHECK ADD  CONSTRAINT [FK_CategoriesBrands_Categories] FOREIGN KEY([IdCategory])
  REFERENCES [dbo].[Categories] ([IdCategory])
  GO
  
  ALTER TABLE [dbo].[CategoriesBrands] CHECK CONSTRAINT [FK_CategoriesBrands_Categories]
  GO
  
  CREATE TABLE [dbo].[Products](
      [IdProduct] [bigint]  NOT NULL,
      [ProductName] [nvarchar](200) NULL,
      [IdCategory] [bigint] NULL,
      [IdBrand] [bigint] NULL 
      CONSTRAINT [PK_Products_IdProduct] PRIMARY KEY CLUSTERED (  [IdProduct] ASC )
  ) ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Brands] FOREIGN KEY([IdBrand])
  REFERENCES [dbo].[Brands] ([IdBrand])
  GO
  
  ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Brands]
  GO
  
  ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([IdCategory])
  REFERENCES [dbo].[Categories] ([IdCategory])
  GO
  
  ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Categories]
  GO
  </code></pre>
  
  <p>Here are scripts to insert some sample data in tables </p>
  
  <pre><code>INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(1, 'Appliances', NULL)
  INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(2, 'Electronics', NULL)
  INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(38, 'Phones &amp; Mobile Devices', 2)
  INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(39, 'Cameras &amp; Photography', 2)
  INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(115, 'Mobile Phones', 38)
  INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(121, 'Digital Cameras', 39)
  
  
  INSERT INTO Brands (IdBrand, BrandName) VALUES(1, 'Nokia')
  INSERT INTO Brands (IdBrand, BrandName) VALUES(2, 'Samsung')
  INSERT INTO Brands (IdBrand, BrandName) VALUES(3, 'Canon')
  
  
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(1, 2, 1)
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(2, 38, 1)
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(3, 115, 1)
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(4, 2, 2)
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(5, 38, 2)
  INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(6, 115, 2)
  
  
  INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(1, 'AAAA', 115, 1)
  INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(2, 'BBBB', 115, 2)
  INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(3, 'CCCC', 121, 3)
  INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(4, 'DDDD', 115, 1)
  INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(5, 'EEEE', 121, 3)
  </code></pre>
  
  <p>So far I have tried this which is giving me top 5 brands without category </p>
  
  <pre><code>SELECT TOP 5
      b1.IdBrand, 
      ISNULL(b1.BrandName, '') AS BrandName, 
      count(p.IdProduct) AS 'ProductsCount'
  FROM Brands b1
  LEFT OUTER JOIN Products p
  ON b1.IdBrand = p.IdBrand 
  GROUP BY b1.IdBrand, b1.BrandName 
  ORDER BY ProductsCount DESC
  </code></pre>