Problem with using NOT IN

[woooinred]

table 'loan':
+--------+
| loanID |
+--------+
| 1 |
| 2 |
| 5 |
| 10 |
| 15 |
+--------+

table 'commitmentAssigned':
+--------------+--------+
| commitmentID | loanID |
+--------------+--------+
| 8 | 1 |
| 9 | 1 |
| 9 | 10 |
+--------------+--------+


I run this query:
select * from loan WHERE loanID NOT IN ("SELECT loanID from commitmentAssign WHERE commitmentID = 9 ");

and hoping to see a return set with loanID = 2, 5, 15 but instead, it gives me:

+--------+
| loanID |
+--------+
| 1 |
| 2 |
| 5 |
| 10 |
| 15 |
+--------+

Can any1 help correcting my query?? THANKS in advance!

 

[r937]

remove the double quotes, because they turn the subquery into a string of characters, and your loanID is not going to be equal to a string of characters, is it


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[woooinred]

I didn't include the double quote at first, and this is what I got:
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT loanID from commitmentAssign WHERE commitmentID =8)' at

 

[r937]

ah, well, that error is different

it means you're not on release 4.1



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[woooinred]

You are damn right. 4.0.22 is what I am having.
Any idea to do what I am looking for with this release?

 

[r937]

you're in luck, eh

not all subquery scenarios have such a convenient workaround --

select loan.loadID
  from loan 
left outer
  join commitmentAssign
    on loan.loadID
     = commitmentAssign.loadID 
   and commitmentAssign.commitmentID = 9
 where commitmentAssign.loadID is null

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[woooinred]

Thansk r937!