select *,count(case_id)
from blah
where (status = 'FAILED')
group by case_id
having count(id)>1
having applies to grouped results.
results:
FAILED,1,453,0,0,0,2
FAILED,6,464,3,2,5,2
FAILED,7,468,0,2,5,2
FAILED,8,469,0,2,5,2
without the having clause, you get:
FAILED,1,453,0,0,0,2
FAILED,6,464,3,2,5,2
FAILED,7,468,0,2,5,2
FAILED,8,469,0,2,5,2
FAILED,21,636,6,0,0,1
Jim Michaels ([EMAIL PROTECTED])
----- Original Message -----
From: "Jim Matzdorff" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 27, 2001 11:33 AM
Subject: How do I get the query results I want?
> lets say i have a query the returns me the following data:
> +--------+----+-------------+-------------+-------------+-------------+
> | status | id | case_id | analysis1 | analysis2 | analysis3 |
> +--------+----+-------------+-------------+-------------+-------------+
> | FAILED | 1 | 453 | 0 | 0 | 0 |
> | FAILED | 6 | 464 | 3 | 2 | 5 |
> | FAILED | 7 | 468 | 0 | 2 | 5 |
> | FAILED | 8 | 469 | 0 | 2 | 5 |
> | FAILED | 13 | 453 | 0 | 0 | 0 |
> | FAILED | 14 | 464 | 0 | 0 | 0 |
> | FAILED | 15 | 468 | 0 | 0 | 0 |
> | FAILED | 16 | 469 | 0 | 0 | 0 |
> | FAILED | 21 | 636 | 6 | 0 | 0 |
> +--------+----+-------------+-------------+-------------+-------------+
>
> this is multiple join amongst several tables to get the above data. now,
> what i really want to be able to do, is to get the 'id' in which there are
> more then 1 'FAILED' status for a particular case_id, ie, for the above,
> i'd get id's 1,6,7,8,13,14,15,16 ... but not 21 because it only has one
> FAILED status for case_id 636.
>
> my problem is i am a bit unsure how (or if) i can do this via a query in
> mysql. i can't group by status (that i know of) because then i lose the
> individaul 'id' columns ... and i can't do a 'having' clause because i
> have no counts.
>
> any help?
> --jim
>
> --------
> When I read about the evils of drinking, I gave up reading.
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
> .
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
