php - Query with multiple values in a column

Question

I have a table like:

id     name            children
1      Roberto         Michael,Dia
2      Maria           John,Alex
3      Mary            Alexandre,Diana

My problem is; I want to find who has a child named Alex.

I can't use "where children = 'Alex'" in SQL because I have more than one names in same cells.

So I use "where children LIKE '%Alex%'" - that looks smart but in the same time i get all start like Alex :( Alexandre or i want to get dia but result is dia and diana :(

how can I get single Alex in that data type?

I hope I can explain my problem with my terrible english :D

Answer 1

The best solution would be to normalize your schema. You should have a separate table with one row for each child, instead of a comma-delimited list. Then you can join with this table to find parent with a specific child. See @themite's answer for an example of this.

But if you can't do that for some reason, you can use FIND_IN_SET:

WHERE FIND_IN_SET('Alex', children)