Getting count from multiple tables [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Getting count from sql tables.

I have already asked earlier on same data and tables but this time requirement is slight different. So please do not vote it to close or downvote. I have 4 tables, as displayed in image 3 tables and another is tbl_Company.:

Now in the map table, the primary keys of the rest three tables. Also in table sub category we have various childs of categ开发者_Go百科ory, marked with category id 2, but if you see in map we have only 4 items with categoryid 2 and has two unique companies 7 and 8.

So what I want is to display when a category is chosen, all its subcategories will get listed with the number of companies. Like in map table cat id 2 has 4 rows and has 12 sub cats (actually in table its 44). SO my output will have all 44 subcat with displaying 4 sub cat has companies and rest 0. Something like this

SubCategoryName  TotalCompanies
---------------  --------------
Badges, Emblems  0
Fashion scarves  1
…                …

and so on.

I used this query

SELECT     tbl_SubCategory.Name AS SubCategoryName, TotalCompanies = (Select COUNT(CompanyId) From tbl_Company_Category_Map WHERE CategoryId=2 )
FROM       tbl_Category RIGHT JOIN
                      tbl_SubCategory ON tbl_Category.Id = tbl_SubCategory.CategoryId
                      LEFT JOIN 

                      tbl_Company_Category_Map ON tbl_SubCategory.Id = tbl_Company_Category_Map.SUbCategoryId 
WHERE     (tbl_Category.Id = 2)
Group By tbl_SubCategory.Name
ORDER BY tbl_SubCategory.Name

but it returns me 4 companies for all 44 subcategories where as my actual output should be only 4 rows should have companies and rest 0

---

This particular query doesn't seem to require the tbl_Category table:

SELECT
  SubCategoryName = tbl_SubCategory.Name,
  TotalCompanies  = COUNT(DISTINCT ccm.CompanyId)
FROM tbl_SubCategory sc
  LEFT JOIN tbl_Company_Category_Map ccm ON sc.Id = ccm.SubCategoryId
WHERE sc.CategoryId = 2

---

You are looking for a group by with rollup :

    SELECT     tbl_SubCategory.Name, tbl_Company_Category_Map.Company_ID,count(tbl_Category.Id)
    FROM       tbl_Category 
    RIGHT JOIN tbl_SubCategory ON tbl_Category.Id = tbl_SubCategory.CategoryId
   LEFT JOIN  tbl_Company_Category_Map ON tbl_SubCategory.Id = tbl_Company_Category_Map.SUbCategoryId 
    WHERE     (tbl_Category.Id = 2)
    Group By Rollup(tbl_SubCategory.Name,tbl_Company_Category_Map.Company_ID)
    ORDER BY tbl_SubCategory.Name

A little more information about the result set the count for just sub cats will have a null for the company id.

---

Have you tried using correlated query? A correlated query is a sub query using a value of a column outside the sub query, in the main query. It would look something like this:

      SELECT     
        tbl_SubCategory.Name AS SubCategoryName, 
        TotalCompanies = (
          Select 
            COUNT(CompanyId) 
          From 
            tbl_Company_Category_Map 
          WHERE 
            CategoryId = tbl_Category.Id) -- Here, it will count using the current cat_id
      FROM       
        tbl_Category 
          RIGHT JOIN tbl_SubCategory 
            ON  tbl_Category.Id = tbl_SubCategory.CategoryId
          LEFT JOIN tbl_Company_Category_Map 

            ON tbl_SubCategory.Id = tbl_Company_Category_Map.SUbCategoryId  
      WHERE     
        (tbl_Category.Id = 2) 
      Group By 
        tbl_SubCategory.Name 
      ORDER BY 
        tbl_SubCategory.Name