MySQL query
| From: | Srinivasan Ramakrishnan | Date: | Thu, 18 Oct 2001 16:06:03 +0000 |
| Subject: | MySQL query | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-71502@lists.php.net to get a copy of this message | ||
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