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:

(Desired) Result:

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?

Answers


Should be able to adapt the GROUP_CONCAT equivalents in this article:

Postgresql GROUP_CONCAT equivalent?


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

See SQL Fiddle with Demo


Need Your Help

Diference between resources in ListFragment

android android-layout android-fragments android-resources android-listfragment

So, I want to create a ListView fragment, but all im getting is blank fragment. My guess is that 3 layout resourses (R.layout.fragment_home) should be diferent, but I can't understand which.

Mobile menu button is unclickable

javascript html css html5boilerplate

I am trying to build a responsive Wordpress site. Link. I am using HTML5 Boilerplate and has been looking through Google and here for the problem.