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)

Answer 2

You should split the data into two tables.

the first would look like this

ID    Name
1     Roberto
2     Maria
3     Mary

And the second like this

ParentId     child
1            Michael
1            Dia
2            John
2            Alex

and so on.

then you could do the query you want without having to worry about like and your data is much more useable

Answer 3

That's why you'd want to have two tables here.

parents:
id  name
1   Roberto  
2   Maria
3   Mary

children:
id  parentid name
1   1        Michael
2   1        Dia
3   2        John
4   2        Alex
5   3        Alexandre
6   3        Diana

And now you can query this much more effectively with a join or an exists:

SELECT *
FROM Parents
WHERE EXISTS(
    SELECT * 
    FROM Children 
    WHERE parentid=Parents.id 
      AND Children.name='Alex'
 )

Answer 4

I would rather make different tables for children and parents something like this.

Table for parents

parent_id     name            
1             Roberto         
2             Maria           
3             Mary     

Table for children

children_id  parent_id  name
1            1          Michael     
2            1          Dia 
3            2          John

.... and so on