Re: [PHP-DB] JOIN problem
am using mysql 3 so i can't do that subquery, how can we do that in mysql 3 ? On Mon, 7 Feb 2005 19:47:08 +, Simon Rees [EMAIL PROTECTED] wrote: On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
Zouari Fourat wrote: am using mysql 3 so i can't do that subquery, how can we do that in mysql 3 ? and neither can anyone else ;-)... rather a pain, but mysql.com have been kind enough to give a detailed explaination on how to rewrite subselect queries as join queries: If you put your brain in gear this page should tell what you need to know: http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html If you get stuck, you know where the list is :-) On Mon, 7 Feb 2005 19:47:08 +, Simon Rees [EMAIL PROTECTED] wrote: On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] JOIN problem
Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
:( nothing On Mon, 7 Feb 2005 14:10:10 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try INNER JOIN? On Mon, 7 Feb 2005 19:47:29 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: that wont work :( and either when changing RIGHT to LEFT JOIN that wont work On Mon, 7 Feb 2005 13:41:01 -0500, Joseph Crawford [EMAIL PROTECTED] wrote: try doing this SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName != b.username) On Mon, 7 Feb 2005 19:22:15 +0100, Zouari Fourat [EMAIL PROTECTED] wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : SELECT a.UserName FROM table1 a RIGHT OUTER JOIN table2 b ON (a.UserName = b.username) and that wont work, as i saw from sql joint docs, using OUTER JOIN significate that we want to join the 2 tables where the inverse of (a.UserName=b.username) is, so it's equivalent of (a.UserNameb.username) and the LEFT option is about to show the lines from the left side table wich is a (table1) in my query why didnt that work fine ? how to resolve it and is my view on sql joins is incorect ? thanks all -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] JOIN problem
On Monday 07 February 2005 18:22, Zouari Fourat wrote: Hello I have 2 tables with two columns in each one (cloned tables) like this : ID int(6) UserName varchar(25) and i would like to select usernames from table1 that doesnt appear in table2 so i did this : Depending on which database you're using you may be able to do this: SELECT a.username FROM table1 a WHERE a.username NOT IN ( SELECT b.username FROM table2 b ) cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php