Getting two or more image identifiers from a single column

Question

I have a MySQL table with one or more numbers separated by commas. These numbers refer to the identifier in another MySQL table, because it also includes the image name.

I want to select a row in which the "image" field contains "1.43" and displays two images with identifiers 1 and 43 from the second table.

Should I have two numbers in different fields? I would prefer to keep them in the same.

Answer 1

use FIND_IN_SET . Assuming you have a table as shown below

Table 1

 +++++++++++ Images +++++++++++ 1,43 

Table2

 +++++++++++++++++ ImageID Image +++++++++++++++++ 1 .... 43 .... 

Request example:

 SELECT b.* FROM Table1 a INNER JOIN Table2 b ON FIND_IN_SET(b.ImageID, a.Images) > 0 

• SQLFiddle Demo

If you have time to change the structure of the table. Please do it. The above query will execute slowly if you do this in large databases.