SQL multiple join from one row with string

Question

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.

This is a horrible database design

user330315
– user330315

2018-05-03 09:51:38 +00:00
Commented May 3, 2018 at 9:51 — user330315
– user330315, Commented May 3, 2018 at 9:51

user330315 · Accepted Answer · 2018-05-03 09:50:09Z

2

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

answered May 3, 2018 at 9:50

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

dani herrera Over a year ago

Nice sentence. May be some advice about to work with un-normalized data may be useful.

dani herrera Over a year ago

oh! Just now I see your comment on Q.

user330315 Over a year ago

@bboumend: create a properly normalized many-to-many relationship with a mapping table. There is plenty of information on the internet regarding that

Laurenz Albe · Accepted Answer · 2018-05-03 09:50:14Z

0

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)

answered May 3, 2018 at 9:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

SQL multiple join from one row with string

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related