Search code examples
mysqlfoxprovisual-foxpro

mySQL 'NOT IN' to vfp equivalent value

I want to know whats VFP9 equivalent to mySQL 'NOT IN'.

To give you the exact purpose. I have two tables and I want to display all the numbers in table1 which don't have any occurrence in table2. 

TABLE1
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

TABLE 2
 2
 3
 4
 8
 9

RESULT:
 1
 5
 6
 7
 10

I have my mySQL code written:

SELECT * FROM table1 WHERE table1.row1 NOT IN (SELECT row2 FROM table2)

Now this code wont run in vfp9, seem it did not recognize NOT or is there a flaw to my code. Any Idea.

Solution

  • Try LEFT JOIN instead:

    SELECT t1.*
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.row1 = t2.row2
WHERE t1.row1 IS NULL;