CODEWITHSUNDEEP
sqlpostgresqljoinduplicatesaggregate
ayrtondenner
ayrtondenner

Reputation: 97

How to join three tables and aggregate a column from the third table?

I have three tables as follows:

Table A:

id
--
1
2

Table B:

id   | table_a
-----+------------
100  | 1
101  | 1
102  | 2
103  | 2

Table C:

id   | table_b | item
-----+---------+-------
200  | 100     | Apple
201  | 100     | Apple
202  | 101     | Orange
203  | 102     | Apple
204  | 103     | Banana
205  | 103     | Rice

I want to get one row for every row in table A, with the contents of table C aggregated. (I guess with the ARRAY_AGG() function?)
Obviously, table A joins with table B which joins with table C. The final table will have the following contents:

id_table_a | item
-----------+---------------------
1          | Apple, Orange
2          | Apple, Banana, Rice

Upvotes: 0

Views: 451

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

You did not specify, but your example also suggests to:

  • ... de-duplicate items per id_table_a.
  • ... sort items by c.id - for practical reasons I suppose the lowest c.id per group of duplicates.

Assuming referential integrity, we don't need to involve table a at all. Cheaper.

To remove duplicates while sorting by the minimum id per group, use a two-step aggregation:

SELECT id_table_a
     , string_agg(c.item, ', ' ORDER BY c_id) AS items
FROM  (
   SELECT b.table_a AS id_table_a, c.item, min(c.id) AS c_id
   FROM   b
   JOIN   c ON c.table_b = b.id
   GROUP  BY b.table_a, c.item
   ) c
GROUP  BY id_table_a
ORDER  BY id_table_a;

fiddle

It's typically faster to sort in the subquery once instead of the per-group sort in the above query:

SELECT id_table_a
     , string_agg(c.item, ', ') AS items
FROM  (
   SELECT b.table_a AS id_table_a, c.item
   FROM   b
   JOIN   c ON c.table_b = b.id
   GROUP  BY b.table_a, c.item
   ORDER  BY b.table_a, min(c.id)
   ) c
GROUP  BY id_table_a
ORDER  BY id_table_a;

The manual:

Alternatively, supplying the input values from a sorted subquery will usually work.

Upvotes: 1

Related Questions