data in mySQL column

[Marvin_G]

hello guys, i have 2 or more different data need to be inserted into 1 column in mysql database. Example:

name = john (1st column)
blood_type = B (2nd column)
children = mary,keith,alex (3rd column)

name = kenneth
blood_type =A
children = Oliver,Lich

in my program, i need to display/select name who had one of their children named alex. How do i call it??

[ScallioXTX]

You shouldn't store that information in one table, but in two tables, like

people

id | name   | blood_type
------------------------
1  | John   | B
2  | Kennth | A

children

id | parent_id | name
---------------------
1  | 1         | Mary
1  | 1         | Keith
1  | 1         | Alex
1  | 2         | Oliver
1  | 2         | Lich

And then to query

SELECT
    p.name
FROM
    people AS p
    INNER
      JOIN children AS c
ON
    p.id = c.parent_id
WHERE
    c.name = 'Alex'

OR you could put it all in one table and join that table on itself

people

id | parent_id | name    | blood_type
-------------------------------------
1  | (NULL)    | John    | B
2  | (NULL)    | Kenneth | A
1  | 1         | Mary    | (NULL)
1  | 1         | Keith   | (NULL)
1  | 1         | Alex    | (NULL)
1  | 2         | Oliver  | (NULL)
1  | 2         | Lich    | (NULL)

SELECT
    parent.name
FROM
    people AS parent
    INNER
      JOIN people AS child
ON
    parent.id = child.parent_id
WHERE
    child.name = 'Alex'

The way you're storing the data now doesn't scale and will give you more problems than benefits.

[r937]

rémon, the name column in your SELECT clause needs to be qualified

[Marvin_G]

You shouldn't store that information in one table, but in two tables, like

people

id | name   | blood_type
------------------------
1  | John   | B
2  | Kennth | A

children

id | parent_id | name
---------------------
1  | 1         | Mary
1  | 1         | Keith
1  | 1         | Alex
1  | 2         | Oliver
1  | 2         | Lich

And then to query

SELECT
    name
FROM
    people AS p
    INNER
      JOIN children AS c
ON
    p.id = c.parent_id
WHERE
    c.name = 'Alex'

OR you could put it all in one table and join that table on itself

people

id | parent_id | name    | blood_type
-------------------------------------
1  | (NULL)    | John    | B
2  | (NULL)    | Kenneth | A
1  | 1         | Mary    | (NULL)
1  | 1         | Keith   | (NULL)
1  | 1         | Alex    | (NULL)
1  | 2         | Oliver  | (NULL)
1  | 2         | Lich    | (NULL)

SELECT
    name
FROM
    people AS parent
    INNER
      JOIN people AS child
ON
    parent.id = child.parent_id
WHERE
    child.name = 'Alex'

The way you're storing the data now doesn't scale and will give you more problems than benefits.

What does the id column in children table does? what if different parents have the same child name?

[ScallioXTX]

rémon, the name column in your SELECT clause needs to be qualified

Ah yes, good catch.

For query1 it should be SELECT p.name, and for query 2 it should be SELECT parent.name
I've amended my previous post.

What does the id column in children table does?

At this moment nothing, but it might be needed for later use. Plus, we need at least one column to be unique for referencing purposes. (well, not strictly so, but it's very handy nonetheless)

what if different parents have the same child name?

Just put it in there multiple times, no problem.

people

id | name   | blood_type
------------------------
1  | John   | B
2  | Kennth | A

children

id | parent_id | name
---------------------
1  | 1         | Oliver
1  | 1         | Lich
1  | 2         | Oliver
1  | 2         | Lich

In that case John has two children named Oliver and Lich, and Kenneth also has two children named Oliver and Lich.

There is another solution in which you only need to store unique child names once to save storage, but given how dirt cheap disk space is now, plus that it's less semantically correct, I really wouldn't opt for that.

[r937]

less semantically correct? wha?

[ScallioXTX]

less semantically correct? wha?

Because you don't know if they have the same child, or a different child who happens to have the same name. When using separate tables this distinction can be made. When using a many-to-many table however it can not.

[r937]

different children with the same name would require different PKs

anything else, and ur doin it wrong™

[ScallioXTX]

different children with the same name would require different PKs

anything else, and ur doin it wrong™

I know. That's why I said 'there is another way I but highly recommend against it'

[r937]

that method works fine, but it's needlessly complex

it would store each child name once, right? so that automatically means a separate name table, and then the actual child data row (in its parent-child table) would use a FK to get the name, but it would still have it's own unique PK separate from the name

anything else, and ur doin it wrong™

[Marvin_G]

Thanks for all the information but i just curious, is it possible select 1 out of 3 different data in 1 column?

[ScallioXTX]

Thanks for all the information but i just curious, is it possible select 1 out of 3 different data in 1 column?

No, that's why we suggested using two tables instead.