DaniWeb Forum Index > Web Development > ASP

ASP RSS

Select Statement problem

Please support our ASP advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!

•

Join Date: Jan 2009

Posts: 1

Reputation: OnlineD is an unknown quantity at this point

Solved Threads: 0

OnlineD

Offline

Newbie Poster

Select Statement problem

Jan 23rd, 2009

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?

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
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