Return one row with many records from right table
I have two tables. One is a listing of products, and one is a listing of their many attributes. (This has been simplified from my original model. ;) )
Table prods has two columns: id and name. Table taxons has three columns: id, name, and prod_id.
Prods has four records:
id | name = = = = = = 1 | boat 2 | tree 3 | lamp 4 | soda
Taxons has fifteen records:
id | name | prod_id = = = = = = = = = = = = = = 1 | bright | 3 2 | breezy | 1 3 | green | 2 4 | wet | 1 5 | sturdy | 2 6 | shady | 2 7 | antique | 3 8 | deciduous | 2 9 | buoyant | 1 10 | fizzy | 4 11 | calming | 2 12 | carefree | 1 13 | big | 1 14 | sweet | 4 15 | brass | 3
What I would like is a query that join taxons onto prods using prods.id and taxons.prod_id, but only return 4 rows, so something like this:
1 | boat | breezy | wet | buoyant | carefree | big 2 | tree | green | sturdy | shady | deciduous | calming 3 | lamp | bright | antique | brass 4 | soda | fizzy | sweet
I'm not sure where to go with this, I've tried various combinations of unions and joins, but haven't found anything that works. We're using postgres. Any ideas?
Should be able to adapt the GROUP_CONCAT equivalents in this article:
To get all of the values in one column you can use array_agg():
select p.id, array_agg(t.name) list from prods p inner join taxons t on p.id = t.prod_id group by p.id