The following will do the trick:
select * from book_hold
group by tcc_cn
having updated=min(updated) and max(status='NOTIFIED')=0;
We use the fact that (status='NOTIFIED') will be 0 when false and 1 when
true,
so this gives a way to select rows for which another row with the same
tcc_cn
and updated has status='NOTIFIED'.
(An alternative, perhaps less obscure, way is to select all of the tcc_cn's
that
ARE notified into a temporary table and then use an outer join, selecting
for
a null in the temporary table. You still have
to do the group by to select the min(updated), however.)
Hope this helps.
+--------+------------+----------+
| tcc_cn | updated | status |
+--------+------------+----------+
| 990227 | 2002-03-08 | WAITING |
| 990227 | 2002-03-10 | WAITING |
| 990228 | 2002-03-08 | WAITING |
| 990228 | 2002-03-15 | NOTIFIED |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from book_hold
-> group by tcc_cn
-> having updated=min(updated) and max(status='NOTIFIED')=0;
+--------+------------+---------+
| tcc_cn | updated | status |
+--------+------------+---------+
| 990227 | 2002-03-08 | WAITING |
+--------+------------+---------+
1 row in set (0.00 sec)
> Date: Thu, 21 Mar 2002 15:44:42 -0700 (MST)
> From: Jeff Shipman - SysProg <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: Complex query
>
> I've got a mysql table and I need to perform
> a complex query on it, but I'm not sure how
> to formulate it.
>
> This is what I need to do:
>
> select id,tcc_cn,entered,updated,enteredby,num_bump,
> status FROM book_hold WHERE
>
> 1) There are no books with the tcc_cn that already
> have a status of 'NOTIFIED'.
> 2) Its updated date is the MIN(updated) for that
> tcc_cn.
>
> So, for example, if I have the following items:
>
> tcc_cn updated status
> -------------------------
> 990227 03-08-02 WAITING
> 990227 03-10-02 WAITING
> 990228 03-08-02 WAITING
> 990228 03-15-02 NOTIFIED
>
> I would get information back for
> this item:
>
> 990227 03-08-02 WAITING
>
> Is there a good way to do this in one or
> two queries?
>
> Any tips would be most appreciated. Thanks
> in advance!
>
> Jeff Shipman E-Mail: [EMAIL PROTECTED]
> Systems Programmer Phone: (505) 835-5748
> NMIMT Computer Center http://www.nmt.edu/~jeff
---------------------------------------------------------------------
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
