How to get unmatched data from foxpro cursors 2

[Niki_S]

Cursor 1

PoID     Factory
1        SGL
2        SFC
3        VTM
4        SFC
5        SGL

Cursor 2

PoID     Factory
2        SFC
5        SGL

I want my output like this.

PoID     Factory
1        SGL
3        VTM
4        SFC

If PoID is already in my Cursor 2 I don't need to get that records as my final output.
How can I do this?
Thank you.

 

[Chris Miller]

CLEAR

CREATE CURSOR Cursor1 (PoID INT, Factory C(3))
INDEX ON PoID TAG PoID

CREATE CURSOR Cursor2 (PoID INT, Factory C(3))
INDEX ON PoID TAG PoID

INSERT INTO Cursor1 VALUES (1,'SGL')
INSERT INTO Cursor1 VALUES (2,'SFC')
INSERT INTO Cursor1 VALUES (3,'VTM')
INSERT INTO Cursor1 VALUES (4,'SFC')
INSERT INTO Cursor1 VALUES (5,'SGL')

INSERT INTO Cursor2 VALUES (2,'SFC')
INSERT INTO Cursor2 VALUES (5,'SGL')

* solution 1
SELECT * FROM Cursor1 WHERE PoID NOT IN (SELECT PoID FROM Cursor2);
   INTO Cursor Cursor3

* solution2
SELECT Cursor1.* FROM Cursor1 ;
   FULL OUTER JOIN Cursor2 ON Cursor1.PoID = Cursor2.PoID ;
   WHERE Cursor2.PoID IS NULL ;
   INTO Cursor Cursor3

Two things to learn here:
1. IN cannot only be used with a list of values (eg WHERE field in (1,2,3)), but also with a query
2. FULL OUTER join puts all records of both tables side by side and adds NULLs on a side that's missing a match (also from both sides)

If you do the FULL OUTER JOIN without the WHERE clause and with * - not just Cursor.1.* - you'll see what it does and why this WHERE clause gets the result. The full outer join is obviously less intuitive, as you'd be puzzled why this would give any result as the PoID of all data never is NULL. Well, there is a hint on something else to take from this, you're addressing the result without WHERE clauses in the WHERE conditions and there this field comes from Cursor2.PoId, is called PoID_b but can be addressed with it's source field Curosr2.PoID, even in those full outer join records that don't get their value from Cursor2 but are NULL by the full outer join filling the gaps with NULL (which actually means not filling the gaps, but leaving them "unknown" - more like vacuum than just empty). FULL OUTER JOIN gives you a few more options, with the right where conditions you can mix records in Curso2 with records not in Cursor2, but NOT IN is simpler to understand here, of course. It'll also use the PoID index to optimize this "non join".

Chriss

 

[Niki_S]

Thank You Chriss for your uncountable helps