5

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).

Darkly
  • 147
  • 2
  • 9
  • So you are truly saving CSV data in a column. Well don't do that. See [Junction Tables](http://stackoverflow.com/a/32620163) if you are. Normalization, speed, sanity, – Drew Aug 09 '16 at 03:08
  • No CSVs here. The Junction Table is nothing but two FKs pointing at the PKs of the other two tables. I'm trying to do this because I'm trying to create a human-readable view and I don't want to do the concatenation in my application logic. – Darkly Aug 09 '16 at 03:14
  • Human readable view can occur thru a View. Just as long as you are not expecting to get performance out of doing joins with `find_in_set()`. It's one thing to do `group_concat()` for pretty printing. It is another to expect a well-tuned db engine to get data based on the output of pretty printing. – Drew Aug 09 '16 at 03:18

1 Answers1

4

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;
Mak
  • 154
  • 4