1

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

DNA
  • 42,007
  • 12
  • 107
  • 146
user3479658
  • 21
  • 1
  • 4
  • 2
    See normalization. It's pretty fundamental to relational databases. – Strawberry Feb 20 '15 at 23:01
  • 3
    Not a good idea to have a number of children under a table field. It would be better to create a second table where the names of the children are stored and join it to your main table by using a foreign key. – kidA Feb 20 '15 at 23:03
  • 2
    Strawberry and kidA are both saying the same thing: you need to break out "children" into a separate table, then link it back to "parents". The process is called "[normalization](http://www.studytonight.com/dbms/database-normalization.php)". The "link" between parents and children is called a "[foreign key](http://www.tutorialspoint.com/sql/sql-foreign-key.htm)". – FoggyDay Feb 20 '15 at 23:07
  • 2
    And so is FoggyDay ;-) – Strawberry Feb 20 '15 at 23:09
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Aug 28 '18 at 05:31

4 Answers4

9

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)
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

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

thermite
  • 502
  • 6
  • 19
0

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'
 )
Joep Beusenberg
  • 517
  • 6
  • 15
0

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

André Ferraz
  • 1,511
  • 11
  • 29