Discussion Locked

Problem in simple SQL request

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi everybody! I have a little problem here.

I work under Microsoft Access 97.
I have 2 tables : A and B. The key of A are the same fields as the key of B (so, records are "equivalents"). Many records in A have no correspondant in B but all records in B have a correspondant in A.
I want to SELECT all records in A that have NO correspondant in B (of course, the classical "SELECT A.* FROM A,B WHERE A.key=B.key" is the exact contrary that I want!).
Can someone help, please ?
Thanks!

11 replies

by medievaldude

This is what you should use:

SELECT A.* FROM A LEFT OUTER JOIN B ON A.keyfield = B.keyfield
WHERE B.fieldinB IS NULL

fieldinB is any field within B. Also, my example only does a JOIN on one keyfield. You could add more fields by using this syntax before the WHERE clause: AND A.keyfield2 = B.keyfield2...

When you do a LEFT OUTER JOIN, any records within A that do not have a match in B will populate NULL for all those fields in B.

Let me know if you have any more questions or problems with this.

by rayval

Sorry made a typo...

Select * from A where key not in (select key from B);

it means i select all the records in A that the key does not exist in table B (where i used a select statement that gets all the keys).

I hope this helps.

by rayval

Sorry made a typo...

Select * from A where key not in (select key from B);

it means i select all the records in A that the key does not exist in table B (where i used a select statement that gets all the keys).

I hope this helps.

by bob sellman

Actually, Microsoft has a nice little wizard in Access 97 to do exactly what you want (and you can then examine the resulting SQL code by changing the display from design to SQL). It's called the "Unmatched Query Wizard".

Click on new (query) from the database window, select the wizard. Pick your table A, then table B. Select the field(s) that should match in the two tables. You can then click on finish or select specific (or all) fields in table A to be displayed when the query is run.After you select the fields of interest, if you check "Next" you can name the query yourself and either view the results or modify the design (where you will then be able to display the SQL code if you want).

The basic concept of the SQL code is,as mentioned in the responses above, to select all records from table A where A.key is not in (select key from B), but using the wizard is the easiest and quickest way to accomplish what you want. And you get the right code with all of the requireddotted i's and crossed t's.

This discussion is closed to new replies.