1

I have 2 tables linked together via 3rd association table:

TABLE NAME: lot
id |   description  | <other multiple columns> |
1  | descr_string_1 | ...                      |
2  | descr_string_2 | ...                      | 


TABLE NAME: id_class
id | code |   name  |   
1  |  01  | class_1 | 
2  |  02  | class_2 |
3  |  03  | class_3 |


TABLE NAME: association_lot_id_class
lot_id | class_id |
1      |    1     | 
1      |    2     |
2      |    3     |

I'm trying to make a new table based on lot containing concatenated data on related classes:

TABLE NAME: new_table_lot
id |   description  | <other multiple columns> | class_codes |     class_names    |
1  | descr_string_1 | ...                      |   "01, 02"  | "class_1, class_2" |
2  | descr_string_2 | ...                      |     "03"    |      "class_3"     |

I've tried to use string_agg with different (definitely, wrong) variations based on other SOF answers (e.g. PostgreSQL - JOIN on string_agg) but no luck

SELECT alic.id_class_id, alic.lot_id, ic.code
  FROM association_lot_id_class alic
  JOIN id_class ic
  JOIN (
    SELECT id_class_id, string_agg(id.code, ',') AS codes 
    FROM codes
    GROUP BY id_class)
 
Krank
  • 141
  • 1
  • 8

0 Answers0