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

Question

I have three tables: items, junction, and properties. I have five items (A through E) and five properties (1 through 5). Via the Junction Table, I have assigned properties as follows:

A: 1, 3, 5
B: 1, 2, 3
C: 1, 4, 5
D: 1, 2, 3
E: 1, 4, 5

When I run the following query, I get a lovely fifteen-record cartesian product (as one would expect).

SELECT I.id, I.item_name, P.property_name FROM scratch.items I
JOIN scratch.junction J ON J.item_id = I.id
JOIN scratch.property P ON J.property_id = P.id;

What I want to do is concatenate each item's property names into a single field so I can spit them out like this:

Record  |  item_id  |  item_name  |  properties
----------------------------------------------------------------------------
     0  |  A        |  Item A     |  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 my contrived example here, each item can have any number of properties (including zero).

Answer 1

You can use group_concat function like this:

SELECT I.id, I.item_name, group_concat(P.property_name) as properties
FROM scratch.items I
JOIN scratch.junction J ON J.item_id = I.id
JOIN scratch.property P ON J.property_id = P.id
group by I.id;