Professional Web Applications Themes

Using COUNT() In WHERE Clause - ASP Database

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 ...

  1. #1

    Default Using COUNT() In WHERE Clause

    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?
    MDW Guest

  2. #2

    Default RE: Using COUNT() In WHERE Clause

    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?
    John Scragg Guest

Similar Threads

  1. #40218 [NEW]: Add Else clause to while
    By jbailey at raspberryginger dot com in forum PHP Bugs
    Replies: 2
    Last Post: January 24th, 03:08 PM
  2. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  3. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  4. connect by clause
    By shankar72 in forum Oracle Server
    Replies: 0
    Last Post: July 10th, 08:56 PM
  5. help on join in from clause
    By Jen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139