Table 1
| berechtigen_ids: _int4 |
|---|
| {1,2,3} |
| {1} |
| {2} |
Table 2
| id | name |
|---|---|
| 1 | Karl |
| 2 | Fritz |
| 3 | Chlodwig |
View 3
| berechtigte |
|---|
| Karl, Fritz, Chlodwig |
| Karl |
| Fritz |
I have table 1 and table 2, I want to achieve view 3. How can I do that?
Add a comment
|
2 Answers
You can use a lateral join or subquery:
select (select string_agg(t2.name, ', ')
from table2 t2
where t2.id = any(t1. berechtigen_ids)
) as names
from table1 t1;
Here is a db<>fiddle.
Note that this does not use aggregation, so if two rows have the same ids, there are still two rows in the result set.
Comments
You can do the following:
select string_agg(name, ', ') as berechtigte
from table2 t2
join table1 t1
on t2.id = any(t1.berechtigen_ids)
group by berechtigen_ids
Though this code won't run properly if you have duplcates in the table1. If that's the case I would suggest adding a new column id in the table1 and grouping by it.
Comments
