I think i found a way to do this: in case anybody is interested:
select customer from purchases group by customer having sum(case when purchase = 'freezer' then 1 else 0 end) = 0; I am finding that SQL is trickier (and more powerful) than I thought originally!! Murad Nayal wrote: > > 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 > > > > > > > > > > > > --------------------------------------------------------------------- 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
