mysql - How do i concatenate information from a foreign key? -

i realised i've worded poorly i'm trying find out if can within insert statement, not when trying output data

this might sound confusing can explain it.

i have 2 tables, expertise (parent) , department (child)

in expertise have :

exp_id int(2) primary key  exp_name varchar(30) 

in department have:

dep_id int(2) primary key   dep_name varchar(30)   exp_id int(2) foreign key 

i not want outcome department rows this:

dep_id dep_name exp_id

1    accounting       32 1    accounting       27 1    accounting       29 

i want this

dep_id dep_name      exp_id 1    accounting      32, 27, 29 

so there multiple rows within row, if makes sense.

i believe concatenation have work with, i've never used before , looking help

i realised i've worded poorly i'm trying find out if can within insert statement, not when trying output data

you should able use group_concat, ie:

select dep_id, dep_name, group_concat(exp_id) exp_id   department   group dep_id, dep_name 

you should consider normalisation department table, violating first normal form due repeating groups of dep_id, dep_name. reference here

edit

i see updated question want data stored in way in table, not displayed.

the answer is: don't.

delimited fields impossible deal with, , around horrible in rdbms. if absolutely must have way, use above query create view, , @ least store real data in proper, normalised fashion.

to re-iterate. please don't this. kittens die.