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

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)