RE: [PHP] MySQL query

select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL; http://www.mysql.com/doc/J/O/JOIN.html -----Original Message----- From: Srinivasan Ramakrishnan [mailto:cheeni@cyberwaveindia.com] Sent: 18 October 2001 17:06 To: php-general@lists.php.net Subject: [PHP] MySQL query Hi all, I'm looking at the following scenario: I have MySQL two tables with usernames in both of them, I need to get usernames(A) - usernames(B) In Oracle I would use: SELECT username FROM user MINUS SELECT username FROM task_assignment Since MySQL does not support MINUS, I tried using the following SELECT DISTINCT A.username FROM user A WHERE A.username NOT IN(SELECT B.username FROM task_assignment B); Here's what I get from MySQL: mysql> SELECT DISTINCT A.username FROM user A WHERE A.username NOT IN(SELECT B.username FROM task_assignment B); ERROR 1064: You have an error in your SQL syntax near 'SELECT B.username FROM ta sk_assignment B)' at line 1 It appears to me that MySQL's NOT IN cannot handle recordsets, so as a fix I'm currently doing like so, which works, but I'd really like to get it as a single MySQL statement. $query = "SELECT DISTINCT username FROM task_assignment"; $db_result = mysql_query($query); $data = mysql_fetch_array($db_result); $not_in = $data[0]; while($data = mysql_fetch_array($db_result)){ $not_in .= ', ' . $data[0]; } $query = "SELECT DISTINCT username FROM user"; $query .= "WHERE username NOT IN($not_in)"; Any help will be appreciated. Cheers, -Srini -- http://www.symonds.net/~sriniram -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net