query against comma delimited field

[cfquest]

I can't seem to find out how to do this correctly. I have a field with comma
delimited
text that I need to query against to find a match.

Scenario: A school logs in and would like to see names of volunteers for their
school only.
The Names of volunteers come from their web app (Vol table) where they also
indicate
certain school(s) (VolMore table) they would like to volunteer at. Normally a
volunteer will only select
one preferred school but we do have some cases where they select more than one
school
from a list box on their web app. If they do select more than One then that
info is inserted into
a db field as comma delimited text. I am trying to query that comma delimited
text for the school id
which is identified in a session once the school logs in.

I think I'm pretty close but can't figure the proper setup for the Where
clause.

ANY assistance will be greatly appreciated. Thanks!



<!--- Query to get Preferred School --->
<cfquery name="qGetSchool" datasource="dsn">
SELECT Vol.FName, Vol.LName, VolMore.PrefSchool
FROM Vol, VolMore
WHERE VolMore.PrefSchool = '#GetUser.SchID#'
</cfquery>



<!--- Loop through Preferrred School listing & output --->
<cfset list_of_schools = ListToArray(#qGetSchool.PrefSchool#)>


<cfloop index = "LoopCount"
from = "1"
to = #ArrayLen(list_of_schools)#>


<!--- Query to get Volunteers --->
<cfquery name="qGetVol" datasource="dsn">
SELECT Vol.FName, Vol.LName, VolMore.PrefSchool
FROM Vol, VolMore
WHERE VolMore.PrefSchool = '#GetUser.SchID#'
</cfquery>


<cfoutput>
#qGetVol.LName#, #qGetVol.FName
</cfoutput>
</cfloop>

[philh]

Hi cfquest,

I have a field with comma delimited text...

There's your trouble. This is not a correctly-designed database structure, so
there's no "correct" way to do it and you're SOL as far as SQL syntax is
concerned. The alternative, and a bad one at that, is to write a whole bunch of
CF to spin through the comma-delimited field, treat it as a list and use CF
list functions (listfind, listgetat, etc.) to find the values you're looking
for.

Actually there's one other alternative: redesign this DB in proper relational
format.
For the volunteers' VolMore info, you need:
Schools Table
Volunteers Table
Volunteers/Schools Table

You can then have the database do the work of getting the right records. It
would be wicked fast and the data work would be done where it belongs: on the
data server.

HTH,

[cfquest]

Actually there's one other alternative: redesign this DB

I thought this db was in "proper format." I do have a School Table (the query
that logs in the school & assigns the session) and the Volunteer Table (Vol &
VolMore) that retrieves the PrefSchool list. The only field that I can use to
tie these tables (School/Volunteer) together is by the PrefSchool field since
Volunteers aren't assigned per say to a particular school. This page will act
as a "call list" of volunteers wanting to help at their school.

Thanks for your suggestions.

[Kronin555]

So you have a Schools table, with an ID field. And you have a Volunteers table
with an ID field. What's the point of the VolMore table? is it just a 1-1
relationship with the Vol table?

If so, and all that's in there is their preferred school, change the VolMore
table to be a mapping table between Volunteers and their preferred Schools.
Name it something like VolSchoolPrefs or something. All it has is 2 columns:
volunteer ID and school ID. That way it replaces your comma-delimited list
field and follows relational database design principles.

If there's more information than just the preferred school list in the VolMore
table, add a new table. If there's a 1-1 mapping between Vol and VolMore
though, I question the reasoning and necessity behind separating them into 2
tables.

There's really no _nice_ way to handle what you've done (put a comma-delimited
list of IDs that should be foreign key references to a different table). SQL
doesn't provide methods to handle it because you aren't supposed to do it. You
could theoretically do it in coldfusion, but you'd need to basically pull all
your volunteers from the database, then loop over them, putting the prefschool
field into a ListContains call for their school ID. Not very lightweight at all.

[mxstu]

I agree with philh. If I'm understanding you correctly, you have the right
number of tables (3), but the structure of your "VolMore" table is wrong.
Instead of storing a comma delimited list of schools (ex. "18,2,38") in the
"PrefSchool" column, you want enter each school ID as a separate record like
this (see example 1)

With this structure it would be extremely simple to retrieve a list of
volunteers for a particular school (see example 2)





Example 1:

Table [VolunteerSchools]
ID VolunteerID SchoolID
==================================
1 22 1 (Jack Bell / ABC School)
2 22 3 (Jack Bell / EFG School)
3 24 1 (Mary Smith / ABC School)

*Note: The ID columns should have a numeric data type (not text).

Example 2:

--- SQL is not tested
SELECT v.FirstName, v.LastName
FROM Volunteers v, VolunterSchools vs
WHERE vs.SchoolID = #CurrentSchoolID# AND
vs.VolunteerID = v.VolunteerID

[cfquest]

Excellent example. Now I see what you two are saying. I figured I was hoping
for a simple solution like "using some magic code" to only loop through just
the comma delimited field and not all the thousands of records.

Thanks

[mxstu]

You're welcome. It may take a short time to re-design your table now, but as
philh pointed out it is a much better structure, your queries will be faster
and you won't have to worry about this list issue again. In the long term it
will save you many headaches.

If you have any problems or questions about how to insert the data, feel free
to post back and someone will help you with it.