Re: help on NOT EXISTS SQL query
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
Re: help on NOT EXISTS SQL query
Dear Murad, > 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? You have probably tried something like SELECT ... WHERE IS NOT NULL. To emulate a "not exists" subselect, you would use SELECT ... WHERE IS NULL. > BTW: when do you think mysql 4.1 would be stable enough for robust use As I hear, MySQL 4.1-alpha will be released very soon, probably in January. My guess for MySQL 4.1-gamma (the release declared as stable, meaning there are lots of installations in production environments that have proven stable) is August 2003. Any other guesses? Monty? ;-) Regards, -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: "Murad Nayal" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Monday, December 09, 2002 4:37 PM 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 > - 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
Re: help on NOT EXISTS SQL query
Hello Stefan, thanks for the feedback. I think I probably misstated my problem. I just emailed a more explicit example of the sort of thing I am trying to do. for the sake of completeness I'll reproduce it here: 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 the goal is to find all customers that have never bought a freezer. am I correct in interpreting your suggestion, applied to this case, as the query: select customer from purchases where purchase != "freezer" is null i tried and it returned zero rows. probably because purchase != freezer is either true or false and neither value is null! what am i missing? Murad "Stefan Hinz, iConnect (Berlin)" wrote: > > Dear Murad, > > > 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? > > You have probably tried something like SELECT ... WHERE IS NOT > NULL. To emulate a "not exists" subselect, you would use SELECT ... WHERE > IS NULL. > > > BTW: when do you think mysql 4.1 would be stable enough for robust use > > As I hear, MySQL 4.1-alpha will be released very soon, probably in January. > My guess for MySQL 4.1-gamma (the release declared as stable, meaning there > are lots of installations in production environments that have proven > stable) is August 2003. Any other guesses? Monty? ;-) > - 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
Re: help on NOT EXISTS SQL query
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
RE: help on NOT EXISTS SQL query
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 > > > > - 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
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
