3

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.

Charles
  • 50,943
  • 13
  • 104
  • 142
Ikong
  • 2,540
  • 4
  • 38
  • 58
  • 1
    Your code looks fine. What error are you getting? – Tamar E. Granor Jan 23 '13 at 22:19
  • @TamarE.Granor - yes mySQL code is right but for some reason it still displays numbers from both tables. anyway got it with this code instead `SELECT t1.nvalue FROM release_numbers AS t1 LEFT JOIN customer_numbers AS t2 ON t1.nvalue = t2.nvalue WHERE t2.nvalue is null`. – Ikong Jan 25 '13 at 03:12

4 Answers4

3

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;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
1

Try this:

SELECT * FROM Table1 Right JOIN table2 on Table1.row1 = Table2.row2 WHERE Table1.row1 is not null

I believe your Table1 and Table2 is a cursor,

Eduard
  • 666
  • 1
  • 8
  • 25
0

Tamar's comment is correct. Try running the following code:

CREATE CURSOR table1 (row1 i)
CREATE CURSOR table2 (row2 i)
SELECT table1
FOR x=1 TO 10
    APPEND BLANK
    replace row1 WITH x
ENDFOR
SELECT table2
APPEND BLANK
replace row2 WITH 2
APPEND BLANK
replace row2 WITH 3
APPEND BLANK
replace row2 WITH 4
APPEND BLANK
replace row2 WITH 8
APPEND BLANK
replace row2 WITH 9
SELECT * FROM table1 WHERE table1.row1 NOT IN (SELECT row2 FROM table2)

It works fine.

Ed Pecyna
  • 401
  • 3
  • 7
-1

I have created Table1 and Table2 with the exact same values as you.
I have executed SELECT * FROM table1 WHERE table1.row1 NOT IN (SELECT row2 FROM table2)
The result is 1 5 6 7 10.
The code is right, the result also.

gavroche
  • 249
  • 3
  • 12