Thanks Adolfo, this actually won't quite do the trick though. I should have been a bit more specific. the query actually comes up in the context of computational genomics. a similar, perhaps more familiar problem would be something like this:
table id customer purchase 1 c1 microwave 2 c1 car 3 c1 freezer 4 c2 car 5 c2 microwave 6 c3 car 7 c3 CD player etc. the idea is to pull out all the customers who have never purchased say a freezer: if you do select customer from table where purchase != "freezer" you'll get all the INSTANCES of customer purchasing something other than a freezer. i.e. you'll get c1,c2,c3. although c1 did purchase a freezer. my best guess of how to do this in SQL was select distinct t1.customer from table as t1 where not exists (select NULL from table as t2 where t1.customer = t2.customer and t2.purchase = 'freezer') - does that look about right for the purpose I mentioned? - now how do you do that without the subquery (especially considering that the performance of the subquery will probably be horrible) many thanks Murad Adolfo Bello wrote: > > SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.field1=t2.field2 > WHERE t2.field2 IS NULL > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]] On Behalf Of Murad Nayal > > Sent: Monday, December 09, 2002 11:38 AM > > To: MySQL List > > Subject: help on NOT EXISTS SQL query > > > > > > > > > > Hello all, > > > > I need to run query like (in mysql < ver.4): > > > > select * from table1 as t1 where not exists (select NULL from > > table2 as t2 where t1.field1 = t2.field1) > > > > I know you can emulate an 'exists' subquery with a join. but > > I just can't think of a way to emulate a 'not exists' without > > a subquery. probably due to my limited sql experience. any hints? > > > > thanks a lot > > Murad > > > > BTW: when do you think mysql 4.1 would be stable enough for > > robust use (not necessarily mission critical). > > --------------------------------------------------------------------- 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
