Member Avatar for zachattack05

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.

  • 1 Contributor
  • 1 Reply
  • 202 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by zachattack05
Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.