Skip to content
Advertisement

Get all matching records where IDs are joined to a comma separated list

I have two database table like below:

  1. Form
    • Id
    • Name
    • Fields(varchar-255)
  2. FormFields
    • Id
    • Name
    • InputType

Sample data for Form

Id    Name     Fields
1     Form1    1,2,3
2     Form2    1,2

Sample data for FormFields

Id    Name     InputType
1     FName    TEXT
2     Lname    TEXT
3     Email    TEXT

Now I write query as below:

SELECT * FROM FormFields 
WHERE Id IN (SELECT Fields FROM Form WHERE Id = 2)

And I get only one record as below:

Id    Name     InputType
1     FName    TEXT

But I want all the records of FormFields table whose Id is present in the Fields columns of Form table. I want result like this:

Id    Name     InputType
1     FName    TEXT
2     Lname    TEXT

Advertisement

Answer

You cannot use IN to search for specific value inside comma delimited “string”.
You can use FIND_IN_SET for this:

SELECT FormFields.*
FROM Form
INNER JOIN FormFields ON FIND_IN_SET(FormFields.id, Form.Fields) > 0
WHERE Form.Id = 2

Result:

+------+-------+-----------+
| Id   | Name  | InputType |
+------+-------+-----------+
|    1 | FName | TEXT      |
|    2 | Lname | TEXT      |
+------+-------+-----------+

SQL Fiddle


Having said that, I would suggest creating a many-many table that joins Forms to Fields. Rough outline of table structure:

  • Form (id, name)
  • Field (id, name, type)
  • FormField (form_id, field_id)
9 People found this is helpful
Advertisement