Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Less than 4 records

    I need to show and hide a section on a page only if there are less than 4 records whose memberID is 5. This attempt fails!

    SELECT @rownum:=@rownum+1 r
    , id_cr2
    FROM caseReport_ReCert
    , (SELECT @rownum:=-1) r
    WHERE memberID = 5
    AND r<4

    Should I be using different functions?
    thanks
    nick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id_cr2
      FROM caseReport_ReCert
     WHERE memberID = 5 
    GROUP
        BY id_cr2
    HAVING COUNT(*) < 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    I would feel better if you made it look harder thanks makes perfect sense, I guess I have to re-read Simply SQl !

    ta

  4. #4
    Join Date
    Jan 2009
    Posts
    124
    It didn't do what I needed it to do(: What I need is the recordset to be empty if there are less than 4 rows.

    thanks

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    What I need is the recordset to be empty if there are less than 4 rows.
    as far as i know without testing it, that's what my query does

    can you provide a test case?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try

    SELECT id_cr2
    FROM caseReport_ReCert
    WHERE memberID = 5
    GROUP
    BY id_cr2
    HAVING COUNT(*) > 4
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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