sql join without intermediate table (old database)

I’m working with a really old database and I’d like to do a simple join. Unfortunately, there is no intermediate table and it’s not possible to implement it in the near future (I know it’s really bad).

The tables look like this:

tbl_1
id        name      fs
1         Name 1    :1::2:
2         Name 2    :3:


tbl_2

id        location
1         London
2         Zurich
3         New York

Expected result (for tbl_1.id = 1)

id        name      location
1         Name 1    London
1         Name 1    Zurich

How can I achieve that? I tried (didn’t work):

SELECT tbl_1.id, tbl_1.name, tbl_2.location FROM tbl_1
INNER JOIN tbl_2
on tbl_2.id IN (tbl_1.fs)
WHERE tbl_1.id = 1;

This works but I need to use tbl_1.fs:

SELECT tbl_1.id, tbl_1.name, tbl_2.location FROM tbl_1
INNER JOIN tbl_2
on tbl_2.id IN ('1', '2')
WHERE tbl_1.id = 1;

Answer

What about

select table1.id,name,location
from table2
inner join table1 on fs like concat('%:',table2.id,':%')

https://www.db-fiddle.com/f/od1cVrPi1SoZBGiLvQfkP5/0

Advertisement

Answer