Q: how to write this query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have two tables; Table1.ID is 1,2,3,4,5,… Table2.ID is random numbers.
Now I need to write a query that gives me all the ID in Table1 which is not
in Table2.
How should I write it?
Thanks,
 
SELECT Table1.ID, Table2.ID
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID
WHERE Table2.ID IS NULL
 
My first shot would be:

SELECT ID
FROM Table1
WHERE NOT EXISTS(
SELECT * FROM Table2 WHERE Table2.ID=Table1.ID
);

Peter J. Veger, Best Netherlands
 
That's true of course, it was my first shot.
If efficiency matters, I should then "optimize" it to the solution with the
join.

But my solution very precisely mimics the question:
.... a query that gives me all the ID in Table1...
 
Hello,
I have two tables; Table1.ID is 1,2,3,4,5,… Table2.ID is random numbers.
Now I need to write a query that gives me all the ID in Table1 which is not
in Table2.
How should I write it?
Thanks,
Click to expand...

Suggestion 1: Create a new Query. Use the Query Wizard and select the
"Unmatched Query".

Suggestion 2: roll your own. Create a new Query; join Table1 to Table2
by ID. This gives you all the records which *are* in Table2.

Click the Join line and select Option 2: "Show all records in Table1
and matching records in Table2". This gives all the records, whether
they match or not - but the value of ID from Table2 will be NULL for
those Table1 records which don't match; so set a criterion on this
field of

IS NULL

to display only the unmatched ones.

The order of records in either table is immaterial and irrelevant.

John W. Vinson[MVP]
 
Back
Top