Select Statement problem
 

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