SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t1(id varchar(100), name varchar(100));
INSERT INTO t1(id, name) VALUES
('RO1', 'Arnold'),
('RO2', 'Ed'),
('RO3', 'Sal'),
('RO4', 'Teus'),
('RO11', 'Haus');
5 rows affected

CREATE TABLE t2(concat_id varchar(100), concat_name varchar(100));
INSERT INTO t2(concat_id, concat_name) VALUES
('RO3,RO4', 'Teus,Port'),
('RO10,RO15', 'Rar,Tar'),
('RO2,RO6', 'Ed,Kev');
3 rows affected

select *
from t1
where exists (
    select *
    from t2
    where exists (
        select *
        from string_split(t2.concat_id, ',')
        where t1.id = value
    ) and exists (
        select *
        from string_split(t2.concat_name, ',')
        where t1.name = value
    )
)


id
name


RO2
Ed

RO4
Teus