Re: JOIN table where not in other table
On Wednesday 26 July 2006 10:31 am, Peter Lauri wrote: Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; If I understand correct: SELECT * FROM table 1 WHERE id NOT IN (SELECT id FROM table2); -- Chris White PHP Programmer/DBacardi Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
I tried that, but that generates: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2) LIMIT 0, 100' at line 1 Maybe it is a Version issue? What version of MySQL do support sub queries? /Peter -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:37 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:31 am, Peter Lauri wrote: Best group member, I just made up this query, but let us work from this: SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.id=table2.id) WHERE table2.prop IS NULL; If I understand correct: SELECT * FROM table 1 WHERE id NOT IN (SELECT id FROM table2); -- Chris White PHP Programmer/DBacardi Interfuel -- 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]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 10:53 am, Peter Lauri wrote: I tried that, but that generates: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2) LIMIT 0, 100' at line 1 What's the full query look like right now (btw, there was a typo earlier, that should have been table1 and not table 1 :/)? -- Chris White PHP Programmer/DBarkTree Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The query in full was exactly as you wrote it (but without the typo) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 5:56 AM To: Peter Lauri Cc: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 10:53 am, Peter Lauri wrote: I tried that, but that generates: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2) LIMIT 0, 100' at line 1 What's the full query look like right now (btw, there was a typo earlier, that should have been table1 and not table 1 :/)? -- Chris White PHP Programmer/DBarkTree Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. -- Chris White PHP Programmer/DBackItUp Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:05 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. -- Chris White PHP Programmer/DBackItUp Interfuel -- 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]
Re: JOIN table where not in other table
On Wednesday 26 July 2006 11:30 am, Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) Are you using phpMyAdmin? I was told by a coworker that phpMyAdmin adds those limits in. Wondering if taking the LIMIT out might do it. Also, are the tables really named table1 and table2 (Yah, I know.. but I have to make sure :( )? -- Chris White PHP Programmer/DBoy Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN table where not in other table
The tables are of course not named table1 and table2, just using that in the discussion. :) All is working right now, but my solution is NOT that good according to the small amount of logic I have :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 6:43 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:30 am, Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my stupid solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) Are you using phpMyAdmin? I was told by a coworker that phpMyAdmin adds those limits in. Wondering if taking the LIMIT out might do it. Also, are the tables really named table1 and table2 (Yah, I know.. but I have to make sure :( )? -- Chris White PHP Programmer/DBoy Interfuel -- 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]
Re: JOIN table where not in other table
Peter feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) It's standard SQL and has a name, "exclusion join". Not a bit more "strange" than counting wot's missing. PB Peter Lauri wrote: 4.0.27, so that is probably the reason. Any other way then with a sub query? I solved it with my "stupid" solution, feels strange to JOIN tables and choose rows where the join value is NULL (left outer join) :) -Original Message- From: Chris White [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 27, 2006 6:05 AM To: mysql@lists.mysql.com Subject: Re: JOIN table where not in other table On Wednesday 26 July 2006 11:00 am, Peter Lauri wrote: The query in full was exactly as you wrote it (but without the typo) :) Alright, yes, what is your version of MySQL? I'm in the 5.0.22 series here and that works just fine. No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/399 - Release Date: 7/25/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
