JOIN UNION Query

[Xenophobe]

I have two tables.

The first contains 2 columns, ID and Name:

1, John
2, Mary
3, Bill
4, Troy

The second contains ID references to the first

1
3

I would like to return a recordset of IDs from the first table that DO NOT
exist in the second

2
4

I played with JOIN and UNION, but can't achieve the desired results. How can
this be accomplish?

Thanks.

 

[John Spencer (MVP)]

Use a frustrated OUTER JOIN (Left Join or Right Join)

SELECT Table1.Field1
FROM Table1 LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE Table2.Field2 Is NULL


You can use the query wizard to build an unmatched query. It will look a lot
the SQL above.

 

[Van T. Dinh]

I think John meant

....
WHERE Table2.Field1 Is NULL

HTH
Van T. Dinh

 

[Xenophobe]

That did the trick--thanks!

Use a frustrated OUTER JOIN (Left Join or Right Join)

SELECT Table1.Field1
FROM Table1 LEFT JOIN Table2
ON Table1.Field1 = Table2.Field1
WHERE Table2.Field2 Is NULL


You can use the query wizard to build an unmatched query. It will look a lot
the SQL above.

 

[John Spencer (MVP)]

Whoops! Thanks for the backup. John sure did mean that.