hi peter. thanks, the solution you gave me is close...!!
my actual data is: mysql> select * from SvnTBL; +--------------+----------+----------+---------------------+----+ | universityID | actionID | statusID | _date | ID | +--------------+----------+----------+---------------------+----+ | 1 | 1 | 0 | 2006-12-08 13:12:15 | 1 | | 1 | 2 | 0 | 2006-12-08 13:12:15 | 2 | | 1 | 3 | 0 | 2006-12-08 13:12:15 | 3 | | 2 | 1 | 0 | 2006-12-08 13:12:15 | 4 | | 2 | 3 | 0 | 2006-12-08 13:12:15 | 5 | | 3 | 1 | 0 | 2006-12-08 13:12:15 | 6 | | 3 | 6 | 0 | 2006-12-08 13:12:15 | 7 | | 3 | 3 | 0 | 2006-12-08 13:12:15 | 8 | | 3 | 4 | 0 | 2006-12-08 13:12:15 | 9 | +--------------+----------+----------+---------------------+----+ if i do: mysql> SELECT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); i get, +--------------+ | universityID | +--------------+ | 2 | | 2 | | 3 | | 3 | | 3 | | 3 | +--------------+ 6 rows in set (0.00 sec) what i really want to get is: +--------------+ | universityID | +--------------+ | 2 | | 3 | +--------------+ which would be the unique 'id's..... i've tried to do a 'limit' and group, but i'm missing some thing... thanks -bruce -----Original Message----- From: Peter Bradley [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 12:26 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: query question... Ysgrifennodd bruce: > hi... > > i'm looking at what is probably a basic question. > > i have a tbl with > -id > -action > -status > -date > > ie: > id action status date > 1 0 1 > 1 1 2 > 1 2 3 > --------------------------------- > 2 0 4 > 2 2 5 > > > i need a query to somehow get all id's that don't have an 'action=1', in > this case, id '2' would be the id that should be returned from the query... > > however, i can't quite figure out how to create a query to select the items > that are the ones i'm looking for. > > any help/thoughts would be appreciated! > > thanks > > -bruce > Hi Bruce, Does this do it for you? SELECT t1.id FROM tbl t1 WHERE t1.id NOT IN (SELECT t2.id FROM tbl t2 WHERE t2.id = 1) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
