1

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.

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
user3136074
  • 11
  • 3

1 Answers1

0

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:

**many-to-many relationship**

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • just because you can, it does not mean that you should! – Strawberry Dec 26 '13 at 09:05
  • 1
    @Strawberry I had already added in my answer that do not apply this approach. But for this specific problem I had given the solution and I also want that OP redesign his database structure – Saharsh Shah Dec 26 '13 at 09:09
  • actually these comma separated values are ID in table 2. This table 2 is having multiple rows with the same ID, meaning there are 10 to 15 rows in table2 with the ID 3006 and so on. – user3136074 Dec 26 '13 at 09:10
  • @user3136074 Then you can use `many-to-many` relationship between your tables and create a mapping table which will have `id` column of `tableA` and `id` column of `tableB` – Saharsh Shah Dec 26 '13 at 09:11
  • Table 1 is created temporarily for current date for a user and he selects the multiple options from a pull down list in a form and these options are than inserted in table 1 with comma separated values at the beginning of the day, afterwards when the manager is free he starts filling the data for the options selected, he should get the options populated and than enter the values and here I am stuck to get one particular option at a time than the page should display NEXT and so on. – user3136074 Dec 26 '13 at 09:20
  • I don't have same ID in both these tables, as you had mentioned above INNER JOIN tableB a ON b.id... values in option are the IDs in table 2 – user3136074 Dec 26 '13 at 09:45
  • @user3136074 Then how we can join both tables – Saharsh Shah Dec 26 '13 at 09:47
  • Table 2 is fixed with all the rows populated, based on what the user selected in temporary table 1 for options a form is required to be generated based on the option values which are separated by comma. – user3136074 Dec 26 '13 at 10:10