sql - How Do Concatenate The Cartesian Product From a Junction Table Join in MySQL -

i have 3 tables: items, junction, , properties. have 5 items (a through e) , 5 properties (1 through 5). via junction table, have assigned properties follows:

a: 1, 3, 5 b: 1, 2, 3 c: 1, 4, 5 d: 1, 2, 3 e: 1, 4, 5 

when run following query, lovely fifteen-record cartesian product (as 1 expect).

select i.id, i.item_name, p.property_name scratch.items join scratch.junction j on j.item_id = i.id join scratch.property p on j.property_id = p.id; 

what want concatenate each item's property names single field can spit them out this:

record  |  item_id  |  item_name  |  properties ----------------------------------------------------------------------------      0  |         |  item     |  property 1, property 3, property 5      1  |  b        |  item b     |  property 1, property 2, property 3      2  |  c        |  item c     |  property 1, property 4, property 5      3  |  d        |  item d     |  property 1, property 2, property 3      4  |  e        |  item e     |  property 1, property 4, property 5 ---------------------------------------------------------------------------- 

unlike contrived example here, each item can have number of properties (including zero).

you can use group_concat function this:

select i.id, i.item_name, group_concat(p.property_name) properties scratch.items join scratch.junction j on j.item_id = i.id join scratch.property p on j.property_id = p.id group i.id;