I can't seem to wrap my brain around this one and would really appreciate it if someone has some suggestions! :)

I have two tables, one contains a list of locations (AccountLocations), another a list of contacts that belong to those locations (AccountContacts).

What I would like to do is create a derived table (I'm assuming) that will list all of the specified locations for a given account, and at the same time, check to see if a given contact belongs to each of those accounts, and if it does, switch a bit field in the derived table to True.

Here's an example:

AccountLocations Table
| ID | AccountID | LocationName |
| 1  | 1         | Location 1   |
| 2  | 1         | Location 2   |
| 3  | 2         | Location 3   |

AccountContacts Table
| ContactID | LocationID |
| 100       | 1          |
| 500       | 1          |
| 200       | 3          |
| 400       | 2          |

If i provide the Stored procedure with the variables of 1 for the AccountID and 100 for the ContactID, the resulting table should be:

Results Table
| BitField | LocationName |
| True     | Location 1   |
| False    | Location 2   |

Basically the query would copy all of the possible locations to the results table, and then one by one, go through those locations and "tick/check-off" each one that a given contact belongs to.

Does that make sense? Is this something a stored procedure can do or is this something that should be handled by the application.

I could do it with the application, I just figured it would be best to handle it at the SQL server to prevent 2 sets of data being sent over the wire, just the one would.

I would REALLY appreciate any help with this one. I've been playing with this for a while now and just can't figure it out...SQL is not my strong point.

6 Years
Discussion Span
Last Post by zachattack05

I think I may have actually figured it out.

I had an epiphany when reading about the INTERSECT statement.

I simply created a temporary table, copied all of the locations for a given account into that table, then updated my "Checked" column using the IN operator on a query that selected the list of locations for a given account and contact.

While that probably makes absolutely no sense since you can't see my database schema...it works! I'm beyond excited! Woo hoo!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.