mysql - Is it possible to use a nested select statement as a column that has multiple rows? -

mysql 5.5.36 apache 2.2.15 centos 6.6 php 5.4.31

sorry terrible title, here looking with; have 2 tables, each order has single row , order_items has multiple rows.

order id | date | customer | status  order_items id | order_id | item_id | item_name 

my current code cycling through open orders doing secondary db call grab items , place them single row of html table this:

order_id | date       | customer | status | item_name 1        | 2014-11-20 | 100233   | open   | widget (item id 0004)                                           | widget (item id 0004) 2        | 2014-11-21 | 103327   | open   | widget c (item id 0005)                                           | widget d (item id 0006) 

the desired end result shown in above table, entire thing within singular query if possible, instead of having multiple queries per order displayed.

now, after googling while, haven't been able find trying do. looked @ group_concat, concatenates columns within single table looks like. want concatenate columns table while grabbing rows found.

my attempted sql this:

select    o.id, o.date, o.customer, o.status, oi.id,    (select concat(item_name, ' (item id ', item_id, ')') order_items order_id = o.id) 'item_list'  order o inner join order_items on o.id = i.order_id   o.status = 'open'  order date asc 

that nested select statement can't figure out. so, possible?

when run version of query get:

error code: 1242  subquery returns more 1 row    

you can phrase want group_concat(), think:

select o.id, o.date, o.customer, o.status,        group_concat(i.item_name, ' (item id ', i.item_id, ')' separator ' ') item_list order o inner join      order_items      on o.id = i.order_id  o.status = 'open' group o.id, o.date, o.customer, o.status order date asc; 

i don't understand data layout, in terms of having items on separate lines. version starts new line, more typically comma or semicolon used.