extracting data from a comma-deliniated list in a Mysql database

[College_Kid]

I have a database table entitled meetings. In the meetings table it has the columns named "meeid", "meedate" and "meememberIDs". by using ColdFusion 9 I am able to submit a Standard date in the "meedate" and a comma-delineated list in memberID's who was present from a series of checkboxes from a form. It looks like this :

meeid = 2

meedate= '2014-06-15"

meememberIDs= '3,5,67,78,354, 2345'

Now that I have a record of the date and a comma-delineate list of membersID's that came to a meeting, is there a way using stand SQL to extract the memberID's and match numbers to the primary key to another table entitled "personnel"  wherein each number of the comma-delineated meememberIDs column would match the full name  and email to the member of personnel table?

example:

3 = michael jones - mjones@ptree.com

5= chris ebinger - cebinger@ptree.com

67 = alfred fox - afox@ptree.com

[Carl Von Stetten · Accepted solution]

College Kid,

Storing comma-delimited lists in a single database column is generally considered "a bad idea".  Why? Mainly, for the very reason you are struggling with - relating that data to other tables.  The preferred way to store multiple values would be through a relationship or join table.  Here's how that could work (there might be some subtle issues with the table structure/query syntax below as I don't have any experience with MySQL, only Microsoft SQL Server):

Meetings Table:

meeid (integer)

meedate (date or date/time)

Personnel Table:

personnelID (or whatever it is called in your table) (integer)

fullname (varchar())

email (varchar())

... the rest of the columns in the existing table

MeetingsPersonnel Table:

meeid (integer)

personnelID (integer)

Instead of storing a comma-delimited list of personnel id's in the meememberIDs column of your Meetings table, you would insert a record in the MeetingsPersonnel table for each combination of meeting and personnel.  So in the example above, the three entries you showed would be stored in the MeetingsPersonnel table like this:

meeid     personnelID

2         3

2         5

2         67

Now if you wanted to generate a list of people who attend meetings, you join Meetings to Personnel through the MeetingsPersonnel table:

SELECT p.fullname,
       p.email
FROM meetings m
     INNER JOIN meetingspersonnel mp on m.meeid = mp.meeid
     INNER JOIN personnel p on mp.personnelID = p.personnelID
WHERE m.meedate = '2014-06-15'

If you create appropraite indexes on these tables, this arrangement will perform very, very fast.

The alternative is to write your own function to parse the comma-delimited list into a temporary table, then use the temporary table to be the intermediate join.  This will be highly inefficient, especially as the number of employees who attend a specific meeting increases.

-Carl V.

[BKBK]

To answer the immediate question, if you have a meetingQuery for a particular day, you could run the second query so as to pick out the meememberIDs who attended. Something like

<cfquery name="attendeeDetails">

select memberID, concat(firstname, " ", lastname) as fullname, email

from personnel

where memberID in (#meetingQuery.meememberIDs#)

</cfquery>

This is just a quick-fix, for example, if you are unable to change things. Ultimately, the best solution is the one Carl offers.

[College_Kid]

Thanks Dan, you're correct. The best and cleanest way is  to do it as a separate tables to gather the nessesary primary keys from each table then link them with All with sql. Thanks for your help