SQL Stored Procedure: Selecting Unique Values on One Table and Filtering with Joins?

[zachattack05]

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?

[Momerath]

SELECT DISTINCT a.ID FROM Contacts a, AccountsContacts b WHERE a.ID = b.ContactID AND b.AccountID NOT IN (<account id here>);

Should give you a list of all the contacts that are not linked to the specified account(s).

[zachattack05]

Hmmm...

That's clever! I'll give that a shot!

Thanks!

[zachattack05]

Hmmm
That doesn't seem to work either. I still get the same result.

I know there has to be a way to do this.

[Momerath]

Can you post small examples of the tables, and what you want the output to be? Maybe I'm just not understanding what you are trying to do.

[zachattack05]

Sure!

Here's the current setup and some sample data. Also, this morning in my first post I gave the wrong table names but I doubt it matters. Anyway, the tables and data:

Table: Contacts

| ID |
------
| 1  |
| 2  |


Table: AccountContacts

| LocationID | ContactID |
--------------------------
| 1          | 1         |
| 1          | 2         |
| 2          | 2         |
| 3          | 2         |


Table: AccountLocations

| ID |
------
| 1  |
| 2  |
| 3  |
| 4  |

Okay, so a sample query would be to list out any contacts not associated with a given "LocationID"

So, if I want to know about LocationID 1, the output should be null because LocationID 1 has contacts 1 and 2 associated with it and since those are the only contacts in the contacts table, LocationID 1 has all the contacts.

If I use LocationID 2, the output should be ContactID 1.

If I use LocationID 3, the output should be ContactID 1 also

and finally, if I use LocationID 4, the output should be ContactID 1 and 2

Attached is an image of the table diagrams if it's of any help.

As it stands right now, the query you gave earlier and all of the ones I've tried, when given the LocationID 1, the result is ContactID 2, and if given LocationID 2 or 3, the result is ContactID 1 and 2.

It's making me crazy! Is it a bad table design or am I just missing something?

[zachattack05]

As an addendum:

I am creating a stored procedure here to accomplish this task if that's of any help. My knowledge of SQL stored procedures is EXTREMELY limited. Beyond what the Query Designer offers, many things are over my head or I just don't have knowledge of them. If there is something special that can be done in a stored procedure to achieve this I wouldn't even know where to begin and would love to hear what you might have to offer in that area.

[Momerath]

SELECT DISTINCT ID FROM Contacts
  WHERE ID NOT IN (SELECT DISTINCT ContactID FROM AccountContacts
                       WHERE LocationID = <location id>);

[zachattack05]

Momerath,

As usual, you made my life so much easier. I don't know how you do it. I wish there was a way I could pay you back for all the help you have given me! If I manage to sell my final product I'll make sure you get something out of it :)

Thanks a ton! I was practically crying over that one for a few days, you can only stare at the screen so long before you just give up...thanks! It works perfectly!