Join 2 tables with string_id instead of jointable

Question

I want to join 2 tables but there is no join table between them... I usually use STRING_SPLIT but in this case, I can't figure it out. Maybe I'm just tired... Could you help me please ?

CREATE TABLE ##Provider
(
  id INT,
  p_name VARCHAR(50),
  list_id_dep VARCHAR(250)
)

CREATE TABLE ##Department
(
  id INT,
  d_name VARCHAR(50)
)

INSERT INTO ##Provider (id, p_name, list_id_dep) VALUES 
    (1, 'toto', '/10/11/12/'),
    (2, 'tata', '/09/');

INSERT INTO ##Department (id, d_name) VALUES 
    (9, 'dep9')
    ,(10, 'dep10')
    ,(11, 'dep11')
    ,(12, 'dep12');

What I want is :

id | p_name | d_name
--------------------------
1  | toto   | dep10
1  | toto   | dep11
1  | toto   | dep12
2  | tata   | dep09

I've tried :

select *
from ##Provider p 
inner join ##Department d on STRING_SPLIT(p.list_id_dep, '/') = ???

select *
from ##Provider p 
inner join STRING_SPLIT(p.list_id_dep, '/') dep ON dep.value = ???

select *
from ##Provider p, ##Department d
where (select value from STRING_SPLIT(p.list_id_dep, '/')) = d.id

select *
from ##Provider p, ##Department d
where d.id in (select value from STRING_SPLIT(p.list_id_dep, '/'))

Maybe STRING_SPLIT is not the right way to do it...

Thanks !

Answer 1

You need a lateral join to unnest the string - in SQL Server, this is implented with cross apply. Then, you can bring the department table with a regular join:

select p.id, p.p_name, d.d_name
from ##provider p 
cross apply string_split(p.list_id_dep, '/') x
inner join ##department d on d.id = x.value

Demo on DB Fiddle:

id | p_name | d_name
-: | :----- | :-----
 1 | toto   | dep10 
 1 | toto   | dep11 
 1 | toto   | dep12 
 2 | tata   | dep9