Query of Queries?

[artists_envy]

I have a table named therapists with a field named modalities which contains a
comma delimited list of id #s. I need to loop through a list of modality id #s
and return results for each id #. Explanation - I need to find the
therapist(s) whose modality field contains the modality id in the list i am
looping through. Does that make sense?... In other words i need to search a
table for therapists who offer a certain modality (the therapists modalities
are stored as a comma delimited list in a field). Please help!

[klutzoid]

how about:
select *
from therapists
where modalities like '%1234567%'

[zoeski80]

Hi Andrew

You can do the query like klutzoid suggested first but then I think you'll
need to loop through the query results to make sure that the ID isn't a subset
of another ID.

eg.
therapist modality: 12345,67890
if you're searching for ID = 345 it will find this therapist but they don't
really have that modalidy ID in this list (it is just a subset of modality
12345)


If you are able to modify the DB a better way would be to have a separate
TherapistModality table which therapist_id and modality_id columns
SELECT therapist_id
FROM therapistModality
WHERE modality_id = #id#
(the database would be better normalised then too)

Zoe

[artists_envy]

Thanks to both of you for the help. I had previously used an access DB with a
modalitiesofferred table which held the therapist's id number and the modality
id number(one record for each of the therapist's modalities). I thought
perhaps that i could find a better way to approach this but, it seems that the
best/easiest way is to go back and use the same DB structure(i am using ms sql
now). Thanks again for your help. Andy