Any quick way to get the "complement" of a query? (1 Viewer)

[dcx693]

Let me explain....

I have a query that looks something like this:
SELECT * FROM tbl1 INNER JOIN tbl2 ON (tbl1.Field1=tbl2.Field1) AND (tbl1.Field2=tbl2.Field2) AND (tbl1.Field3=tbl2.Field3)

Now what I want are the records that are not selected, basically what I would get from:
SELECT * FROM tbl1 - the results from the above query.

What's the easiest way to do this? I guess I could run the "full" query and run a delete query, but was wondering if there was a quicker way.

 

[Pat Hartman]

Change the join type to Left and add selection criteria:

SELECT * FROM tbl1 LEFT JOIN tbl2 ON (tbl1.Field1=tbl2.Field1) AND (tbl1.Field2=tbl2.Field2) AND (tbl1.Field3=tbl2.Field3)
Where tbl2.Field1 Is Null;

 

[dcx693]

Pat, works beautifully. Thank you!