query results from comma seperated values and query for each value to display

Question

I have a table with ID, date, and a comma separated field. e.g. values like:

id    date         options

1     2013-12-26   3006,3009,4010
2     2013-12-25   3002,3001,5090
3     2013-12-24   2909,1012,6089
4     2013-12-23   3001,4009,5008

After querying for a particular date e.g. 2013-12-26 I get the result as 3006,3009,4010 Now I have another table 2 with these individual nos. with additional fields.

Based on these results I need to create a form displaying results from these 3006,3009,4010 separately..

Such as: 3006 content of all the rows with this value in a table 2 values may be fixed or some input fields of a form, so if I enter the values in this created form after submitting I should get next no. i.e. 3009 and so on.

I am able to display all the results of these nos. together but I need to get them separated. So that I get results of 3006 SUBMIT FORM results of 3009 SUBMIT FORM results of 4010 SUBMIT FORM. etc.

Answer 1

You can use FIND_IN_SET() function.

Try this:

SELECT a.id, a.date, a.options, b.* 
FROM tableA a 
INNER JOIN tableB a ON FIND_IN_SET(b.id, a.options)

NOTE: As per me this is not good table design that you store multiple IDs in single record. Do not designthis type of tables. Try to create mapping table where both tables IDs where mapped. Try to create database in 1NF or more than that which is applicable

Example of many-to-many relationship: