MySQL CREATE FUNCTION result with multiple records gives error #1172 -

i have following 3 tables:

table "name"        --------------- id     name --------------- 1      book 2      pen   table "color" ------------------ id     color ------------------ 1       red 2       yello 3       green 4       pink   table "both" ------------------------ id     name      color ---------------------- 1      1            1 2      1            2 3      1            3 4      2            2 

and have following function:

    delimiter //      create function get_word(n varchar(20))         returns varchar(10)         reads sql data         deterministic         begin             declare b varchar(20);        select  `color`.`color` b    `name`          left join `both`            on `name`.`id`=`both`.`name`         left join `color`           on `color`.`id`=`both`.`color`         `name`.`name`=n;                  return b;         end//      delimiter ; 

now when run select get_word('pen') returns yellow expect.

but when run code select get_word('book') error: #1172 - result consisted of more 1 row

my question: function works multiple records single record when search "pen"? thanks

update:

if use query without function follow, works fine:

       select  `color`.`color` b    `name`          left join `both`            on `name`.`id`=`both`.`name`         left join `color`           on `color`.`id`=`both`.`color`         `name`.`name`='book';  

and returns:

 **b** red yellow green 

well, seems want resultset (more 1 value)

but mysql stored function cannot return resultset (see doc : restrictions stored functions).

solution 1 : use stored procedure

create procedure get_word(n varchar(20)) begin  select  `color`.`color`     `name`     left join `both`            on `name`.`id`=`both`.`name`    left join `color`           on `color`.`id`=`both`.`color`     `name`.`name`=n;  end 

solution2 : return concatenated string

something "green, yellow, blue" group_concat can easily. see stackoverflow question example