Re: MySQL query

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 curt@gilm.net "Srinivasan Ramakrishnan" <cheeni@cyberwaveindia.com> wrote in message news:002001c157ee$c9bfaea0$7e02a8c0@compuwave.co.in... > 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 >