AZGroups.com

How would I do something like this:

tbLocation
------------
LocationID
Location

tbService
----------
ServiceID
Service


tbLocation_Service
--------------------
Location_ServiceID
LocationID
ServiceID

 

I need to be able to send in a comma delimited string of LocationIDs and return of a UNIQUE list of ServiceIDs for all the services for each location.

I think I need to loop through the comma delimited list, build a table with all the ids and then select distinct from that temp table.  Does that does right?

TIA

Hmm... looks like I'd have to have a sub-loop inside the main loop that loops through the all the services for each location and inserts them into the temp table.  I'm just not sure how to create the sub loob with the returned service IDs for each location I guess.

You could use the below approach if you have to pass in a comma delimited string.
http://www.codeproject.com/database/SPParameters.asp?print=true

If possible I would pass in xml and use OPENXML as shown at
http://www.awprofessional.com/articles/article.asp?p=26499&rl=1

Hope this help,
Chris

Don't know if you have already solved this problem, but I had a similar situation and found an outstanding solution: fn_Split()

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp

This function allows you to pass in any kind of delimited string, and get back a table of the delimited items.  You can then run through that table, join it to another, whatever you need.  This UDF is extremely helpful in a web environment, especially when you have a list of items passed in from a group of checkboxes on a page, or list items in a List Box, etc.

Hope this may still be of use to you.

-- Andrew

---

My new philosophy!
Life's journey is not to arrive at the grave safely in a well preserved body, but rather to skid in sideways, totally worn out, shouting "...holy ***...what a ride!"