CODEWITHSUNDEEP
sqlpostgresql
melissanoelle
melissanoelle

Reputation: 112

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?

Upvotes: 2

Views: 111

Answers (2)

Taryn
Taryn

Reputation: 247620

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

Upvotes: 3

mwigdahl
mwigdahl

Reputation: 16578

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

Postgresql GROUP_CONCAT equivalent?

Upvotes: 2

Related Questions