Professional Web Applications Themes

Query of Queries? - Coldfusion Database Access

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!...

  1. #1

    Default Query of Queries?

    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!

    artists_envy Guest

  2. #2

    Default Re: Query of Queries?

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

  3. #3

    Default Re: Query of Queries?

    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

    zoeski80 Guest

  4. #4

    Default Re: Query of Queries?

    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

    artists_envy Guest

Similar Threads

  1. Query of Queries
    By restlessmedia in forum Coldfusion Database Access
    Replies: 12
    Last Post: September 12th, 09:51 PM
  2. Need Help with Query of Queries
    By LyndonPatton in forum Coldfusion Database Access
    Replies: 6
    Last Post: April 18th, 09:02 PM
  3. query of queries with avg()
    By gogl in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 6th, 09:50 PM
  4. Query of Queries in 7.0
    By Funke in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 17th, 11:12 PM
  5. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139