csv in mysql fields

[meghatronic]

For whatever reason I have a database that contains a foreign key, but these keys are comma seperated within the field.

E.g.
Table1:

animals |blah | blah
1, 3, 5 |blah | blah

Table2:

ref| name | colour | size
1 | dog | black | large
2 | cat | pink | small
3 | ...... | ...... | ......
4 | ...... | ...... | ......
5 | ...... | ...... | ......


I've played around, but can only get the first and last value (so 1 and 5 above), using:

SELECT * 
FROM `Table1` 
JOIN `Table2` ON SUBSTRING_INDEX(animals, ',', 1 ) = `Table2`.ref
UNION
SELECT * 
FROM `Table1` 
JOIN `Table2` ON SUBSTRING_INDEX(animals, ',', -1 ) = `Table2`.ref

 

[matja]

you could use substring_index(substring_index(animals,',',x),',',-1) where x is the position you want, but it may be better to convert the csv values into a new table, eg :

Table1:
id |blah | blah
1 |blah | blah

Table3:
table1_id|table2_ref
1|1
1|3
1|5

Table2:
ref| name | colour | size
1 | dog | black | large
2 | cat | pink | small

where Table1.1 looks-up via Table3.id which then looks up Table3.animal_id into Table2.ref.

Then you can easily select everything from Table2 which is referenced by a item in Table1 :

select Table2.* from Table3
inner join Table2 on Table2.ref = Table3.table2_ref
where Table3.table1_id = 1;

 

[meghatronic]

you could use substring_index(substring_index(animals,',',x),',',-1) where x is the position you want, but it may be better to convert the csv values into a new table, eg :

where Table1.1 looks-up via Table3.id which then looks up Table3.animal_id into Table2.ref.

Then you can easily select everything from Table2 which is referenced by a item in Table1 :

Yeah, in the end, whilst inefficient I used php to generate "insert" commands to create a new link table