Joining Data on a Field with Multiple Values

[PTS]

I have an issue that is way above my database comfort level.

 

Here are two tables I'm working with:

 

signoff

> signoff_id

> signoff_employee

> signoff_supervisor

> signoff_files

 

files

> file_id

> file_name

 

When inserting data into the signoff table, the 'signoff_files' field includes multiple values separated by a comma that correspond to the 'file_id' field in the files table.

 

So if I select the signoff table, I would get something like:

 

"1" , "Employee Name" , "Supervisor Name" , "10001, 10003, 10010";

"2" , "Employee Name" , "Supervisor Name" , "10001";

"3" , "Employee Name" , "Supervisor Name" , "10003, 10010, 10015";

 

Is there any way to replace the values in the 'signoff_files' field with the 'file_name' each value corresponds to?

 

I have no problem using a LEFT JOIN when there is only one 'file_id' value in the 'signoff_files' field, but I don't know what options I have in MySQL to do it for multiple values.

 

Any help or guidance would be greatly appreciated.

 

Thanks,

Paul

[requinix]

the 'signoff_files' field includes multiple values separated by a comma that correspond to the 'file_id' field in the files table.

That's the problem.

 

You need a third table that associates these signoff records with the files being signed off. Using your example it would contain at a minimum

signoff_id | file_id
-----------+--------
         1 |   10001
         1 |   10003
         1 |   10010
         2 |   10001
         3 |   10003
         3 |   10010
         3 |   10015

Then JOIN the three tables together to get the employee/supervisor and the file names.

[PTS]

requinix: Thanks, that makes total sense but that would require a complete overhaul of the complex form I am working with. There's actually far more going on with this form then just the fields and tables I mentioned.

 

That said, short of an easier to implement solution, this seems like a good direction to go.

[requinix]

If you don't fix it now it'll only get worse.