I have two tables that share the same key field (ID). I need to select all records in the first table (Table1) where there is no match in the second table.<br><br>Can anyone help with the SQL for this. <p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Help
- Thread starter WP
- Start date
TheInsider
Programmer
Hi,<br> You would expect that Access would provide the expected results with the following SQL statement:<br><br>SELECT Table2.ID<br>FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID<br>WHERE (((Table2.ID)<>[Table1].[ID]));<br><br>However, Access treats the non-matching/non-existing fields as NULL values, therefore you can use the following SQL statement:<br><br>SELECT Table2.ID<br>FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID<br>WHERE (([Table1].[ID] = NULL));<br><br>or you can use "Is Null".<br><br>Hope that helps you.<br>Rob Marriott
TheInsider
Programmer
After reading your post again, I noticed that it was table 1 that you wanted to display for. Just reverse the names of the tables in my last post.<br>Sorry about that,<br>Rob Marriott
- Status