SQL Query filter based on another column values without parsing it-CodePudding

I would need some help regarding a SQL query that I can't figure out. I have 2 tables like this:

| ID  | NAME   |
| RO1 | Arnold |
| RO2 | Ed     |
| RO3 | Sal    |
| RO4 | Teus   |
.
.
| RO11 | Haus |

The second table:

| Concat_ID | Concat_Name |
| RO3,RO4   | Teus,Port   |
| RO10, RO15 | Rar,Tar |
| RO2, RO6 | Ed, Kev   |

The current methodology is to parse the second table and to replace the filter conditions dinamically based on the rows from the second table:

1st query:

select distinct ID,
NAME
from TABLE1
where ID IN ('RO3','RO4')
and NAME IN ('Teus','Port')

2st query:

select distinct ID,
NAME
from TABLE1
where ID IN ('RO10', 'RO15')
and NAME IN ('Rar','Tar')

And so on...

Is there a way to do this with just a single query?

CodePudding user response：

If I understand correctly you could do this:

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
    )
)

Basically each row in t1 is compared with each row in t2; for each combination you check if the t1.id matches one of the comma separated ids and t2.name matches one of the comma separated names. string_split requires SQL Server 2016 or later.

DB<>Fiddle