Clever Geek Handbook

MySQL 'NOT IN' for equivalent vfp value

I want to know that VFP9 is equivalent to mySQL 'NOT IN'.

To give you a precise target. I have two tables and I want to display all numbers in table1 that have no meaning 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 mySQL code written:

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

Now this code won't work in vfp9, it seems like it didn't recognize NOT

or there is a flaw in my code. Any ideas.

+3


source to share


4 answers


Try LEFT JOIN

this instead:



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


source


Try the following:

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


I believe your Table1 and Table2 are the cursor,

+1


source


Tamar's comment is correct. Try to run 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 great.

0


source


I created Table1 and Table2 with the same values ​​as you. I executed SELECT * FROM table1 WHERE table1.row1 NOT IN (SELECT row2 FROM table2)


Result - 1 5 6 7 10.
The code is correct, the result is also.

-1


source






More articles:


All Articles