Try using a LEFT JOIN against the second table, returning only records with
no match.
Something like:
SELECT DISTINCT A.username
FROM user A LEFT JOIN task_assignment B
ON (A.username = B.username)
WHERE B.username IS NULL;
See JOIN syntax in MySQL documentation for more information:
http://www.mysql.com/doc/J/O/JOIN.html
Good luck!
__________
Curt A. Gilman
Richmond, Virginia, USA
[EMAIL PROTECTED]
"Srinivasan Ramakrishnan" <[EMAIL PROTECTED]> wrote in message
002001c157ee$c9bfaea0$[EMAIL PROTECTED]">news:002001c157ee$c9bfaea0$[EMAIL PROTECTED]...
> 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]
