SQL Brainteaser (Access 97)

Topic

New Reply

Hi there all.

I have a bit of an SQL brain teaser – I’m fairly sure that this can be done with in SQL, just I haven’t been able to figure out how yet.

I have two tables called AL_tbl_IDNumber and EmailAddressTbl, both of them have primary keys called IDNumber.

I want to select all IDNumbers from AL_tbl_IDNumber that is NOT in EmailAddressTbl.

Anyone got any good SQL solutions to this one?

Thanks

James

Reply | Quote

Replies

SELECT AL_tbl_IDNumber.IDNumber
FROM AL_tbl_IDNumber
LEFT JOIN EmailAddressTbl
ON AL_tbl_IDNumber.IDNumber = EmailAddressTbl.IDNumber
WHERE (((EmailAddressTbl.IDNumber ) Is Null));

Reply | Quote

Thanks a million, Charlotte. It works

These Left and Right and Outer Joins consistantly get me tied it knots which I can never undo! I can usually never figure them out without some help.

James

Reply | Quote

I’d suggest that you take advantage of the query grid and build them graphically to avoid getting tied into knots then. Left and right joins are just outter joins. The direction indicates which side the determining table is on, that’s all.

Reply | Quote

Charlotte answered your question, but FYI, when you go to create a new query, a pop-up box offers several choices, such as Design mode and Simple Query Wizard. ONe of the other choices is “Find UnMatched Query Wizard”, which is what you want.

Reply | Quote