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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
