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
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
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 condition IS NOT NULL. To emulate a not exists subselect, you would use SELECT ... WHERE condition 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
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 condition IS NOT NULL. To emulate a not exists subselect, you would use SELECT ... WHERE condition 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
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
