Using COUNT() In WHERE Clause

[MDW]

I know it's verboten. However, I'm not sure how else is the best way to do this.

I've got two tables, tblReferrers and tblReferrals. I'm creating an ASP report to show who among the referrers hasn't met certain miniumum requirements (e.g., they should have a certain number of referrals). The two tables are related by a field called ReferrerID. My result set needs to be who, on tblReferrers, appears in tblReferrers less than a given number - something that COUNT() would be ideal for.

I'm sure I could accomplish this with looping, but I'd like to execute only one SQL statement to get my result set. Any ideas?

[John Scragg]

I think what you are looking for here is the HAVING keyword. COUNT is an aggregate, so you will have to work out your GROUP BY clause, but something like this example should do it.

SELECT a.ReferrerID, a.Blah, COUNT(b.ReferrerID)
FROM tblReferrers a INNER JOIN tblReferrals b ON a.ReferrerID = b.ReferrerID
GROUP BY a.ReferrerID, a.Blah
HAVING COUNT(b.ReffererID) > X

Hope that is helpful,

John


----- MDW wrote: -----

I know it's verboten. However, I'm not sure how else is the best way to do this.

I've got two tables, tblReferrers and tblReferrals. I'm creating an ASP report to show who among the referrers hasn't met certain miniumum requirements (e.g., they should have a certain number of referrals). The two tables are related by a field called ReferrerID. My result set needs to be who, on tblReferrers, appears in tblReferrers less than a given number - something that COUNT() would be ideal for.

I'm sure I could accomplish this with looping, but I'd like to execute only one SQL statement to get my result set. Any ideas?