How to combine Cartesian products from a conversion table Join MySQL

Question

I have three tables: items , junction and properties . I have five elements (A to E) and five properties (1 to 5). Through the connection table, I assigned the 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 wonderful fifteen-record Cartesian product (as you 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 combine the property names of each element into one field so that I can spit them out as follows:

 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 far-fetched example, here each element can have any number of properties (including zero).

Answer 1

You can use the group_concat function as follows:

 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;