Select multiple rows of one column from second table with first table in postgresql

Question

I have two tables, table1 and table2

table1 has the following columns:

ID | OrderNo | Order_Date
---|---------|-----------
1  | O1      | 22/1/2020
2  | O2      | 23/1/2020

table2 has the following columns:

FID | OrderNo | ItemName
----|---------|---------
1   | O1      | TV
2   | O1      | Radio
3   | O1      | Tape
4   | O2      | NoteBook
5   | O2      | Pencil

What sql query would allow me to select:

OrderNo | ItemName
--------|---------
O1      | TV, Radio, Tape
O2      | NoteBook, Pencil

Does this answer your question? [How to concatenate strings of a string field in a PostgreSQL 'group by' query?](https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query) — Bergi, Nov 28 '20 at 22:45
Actually, in your particular example you only need to select from table 2, but even if you wanted to join against table 1 that would be trivial to add. — Bergi, Nov 29 '20 at 10:04

score 0 · Accepted Answer · answered Nov 29 '20 at 00:45

0

There is no need to join for the scenario mentioned in question but in case it is required you may try below query:

SELECT 
t1.OrderNo, string_agg(t2.ItemName, ', ')
FROM table1 t1 
inner join 
table2 t2 on t1.OrderNo=t2.OrderNo
GROUP BY 1;

answered Nov 29 '20 at 00:45

Akhilesh Mishra

5,876
3
16
32

Select multiple rows of one column from second table with first table in postgresql

1 Answers1