CODEWITHSUNDEEP
sqlpostgresql
bboumend
bboumend

Reputation: 510

SQL multiple join from one row with string

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

Answers (2)

Laurenz Albe
Laurenz Albe

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

user330315
user330315

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

Related Questions