Reputation: 510
i have two table like this:
table1
id(int) | desc(TEXT)
--------------------
0 | "desc1"
1 | "desc2"
table2
id(int) | table1_id(TEXT)
------------------------
0 | "0"
1 | "0;1"
i want to select data into table2 and replace table1_id by the desc field in table1, when i have string with ';' separator it means i have multiple selections.
im able to do it for single selection like this
SELECT table1.desc
FROM table2 LEFT JOIN table1 ON table1.id = CAST(table2.table1_id as integer);
Output wanted with a SELECT on table2 where id = 1:
"desc"
------
"desc1, desc2"
Im using Postgresql10, python3.5 and sqlalchemy
I know how to do it by extracting data and processing it with python then query again but im looking for a way to do it with one SQL query.
PS: I cant modify the table2.
Upvotes: 0
Views: 144
Reputation: 248305
That is really an abominable data design.
Consequently you will have to write a complicated query to get your desired result:
SELECT string_agg(table1."desc", ', ')
FROM table2
CROSS JOIN LATERAL regexp_split_to_table(table2.table1_id, ';') x(d)
JOIN table1 ON x.d::integer = table1.id
WHERE table2.id = 1;
string_agg
--------------
desc1, desc2
(1 row)
Upvotes: 0
Reputation:
You can convert the CSV value into an array, then join on that:
select string_agg(t1.descr, ',') as descr
from table2 t2
join table1 t1 on t1.id = any (string_to_array(t2.table1_id, ';')::int[])
where t2.id = 1
Upvotes: 2