I am trying to count how many entires appear in one table which is linked to 2 others, then show the result on a web page.
Table 1 - Companies
CompanyID
CompanyStatusID
FundID
Table 2 - Company_Status
CompanyStatusID
CompanyStatus
Table 3 - Subscriptions
CompanyID
FundID
What I need to do is count how many entries matching each entry in the Company_Status table appear in the Subscriptions table against the variable FundID.
The result might look something like this
Bank = 8
Family Office = 8
Fund of fund (Europe) = 4
HNW = 4
The titles on the left nee dto come from the Company_Status table, the field CompanyStatus and the values are the count for each CompanyStatusID
I have had a stab at it but get a false result, I think what it is doing is counting everything several times since the result is in a repeat region in order to get a result for each entry in the Compnay_Status table. I think what I need to do is add either a JOIN or perhaps a DISTINCT clause but I can not work out how to incorporate this into the statement - perhaps someone here can suggest a better way of doing this?
SELECT S.CompanyStatus, COUNT(S.CompanyStatusID)
FROM Companies C, Company_Status S, Subscriptions F
WHERE C.CompanyStatusID = S.CompanyStatusID AND F.FundID = varFundID GROUP BY S.CompanyStatus