SQL Query Count - joining two tables and getting the count even if it is 0
Ok, working on a query in SQL 2012 right now to join two tables and get the score. How many companies are associated with the parent company are counted. These lists exist in the same Contact table, and they are all related because the primary company ID is listed in the secondary company as CompanyID. I can get the score if the primary has at least 1 secondary, but I cannot get the score if the primary has no secondary value and I need to show that value 0. Here is my SQL query:
SELECT c.ID, c.Company, c.Category, COUNT(c1.ID) AS Secondaries
FROM Contact AS c INNER JOIN Contact AS c1 ON c.ID = c1.CompanyId
WHERE (c.MemberType = 'ORG_M') AND (c1.MemberType = 'ORG_M')
GROUP BY c.ID, c.Company, c.Category
When I do this, I get this information back:
ID Company Category Count
1 Company 1 RS_1 1
2 Company 2 RS_1 1
3 Company 3 RS_1 1
4 Company 4 RS_1 1
What I am missing is the value 0 if the company exists in the company table but does not have a secondary company to it. How do I get it?
source to share
Instead, use outer join
and move the criteria where
to join
:
SELECT c.ID, c.Company, c.Category, COUNT(c1.ID) AS Secondaries
FROM Contact AS c
LEFT JOIN Contact AS c1 ON c.ID = c1.CompanyId AND c1.MemberType = 'ORG_M'
WHERE c.MemberType = 'ORG_M'
GROUP BY c.ID, c.Company, c.Category
source to share