dan... thanks!!! like a charm.. now for the other 200 queries i'm dealing with!!
-----Original Message----- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 1:40 PM To: [EMAIL PROTECTED] Cc: Peter Bradley; mysql@lists.mysql.com Subject: Re: RE: query question... Just add a DISTINCT: SELECT DISTINCT t1.universityID FROM SvnTBL t1 WHERE t1.universityID NOT IN (SELECT t2.universityID FROM SvnTBL t2 WHERE t2.actionID = 2); Dan On 12/8/06, bruce <[EMAIL PROTECTED]> wrote: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
