I'm lost on this one.
I've got 3 tables involved here. An Accounts table, AccountsContacts table and a Contacts table.
The AccountsContacts is a junction table between the two.
The key columns for the tables are:
Accounts: ID
AccountsContacts: AccountID, ContactID
Contacts: ID
I need a way to select from the contacts table all of the contacts that do NOT belong to a specific account.
The problem is, that since one contact can potentially be part of many accounts, by filtering by AccountID, I can get duplicate contacts and potentially contacts that are already a part of the account, simply because they belong to an account that I am filtering to.
So, what I was wondering...
Is it possible to first, uniquely select all rows in AccountsContacts based only on the ContactID, and THEN filter out the AccountID?
When I do this with Access it's a piece of cake, just create a query and use that query in another query. But I'm creating a stored procedure here, and I'm kinda lost.
Any suggestions?