Less than 4 records

oldnickj · #1 (**permalink**) 07-02-12, 10:39

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

r937 · #2 (**permalink**) 07-02-12, 10:57

Code:

SELECT id_cr2
  FROM caseReport_ReCert
 WHERE memberID = 5 
GROUP
    BY id_cr2
HAVING COUNT(*) < 4

oldnickj · #3 (**permalink**) 07-02-12, 11:08

I would feel better if you made it look harder

thanks makes perfect sense, I guess I have to re-read Simply SQl !

ta

oldnickj · #4 (**permalink**) 07-02-12, 11:47

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

r937 · #5 (**permalink**) 07-02-12, 12:13

Quote:

Originally Posted by oldnickj

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?

it-iss.com · #6 (**permalink**) 07-02-12, 12:51

Try

SELECT id_cr2
FROM caseReport_ReCert
WHERE memberID = 5
GROUP
BY id_cr2
HAVING COUNT(*) > 4

Internet Services

Web Development

Digital Marketing

Consumer Tech