Joining table based on list of array in the join table column

I am trying to solve something complicated problem which might be straightforward. So I am trying to join 4 tables. The 4th table has a column named subids which has list of ids and these ids are in the same table in the column id.

So here is the example:

Table A:

IDA | COLA | COLB |
1     ABC   CDE 
2     STR   YUA

Table B:

IDB | IDA| COLE |
1     1   YXA
2     3   ABS

Table C:

IDC | IDB| COLF |
1     1   QRT
2     4   IUQ

Table D:

IDD | IDC | COLH |
1     1   URT,OPQ
2     URT
3     NPQ   

So in Table D's COLH there is URT,OPQ as string so they need to be converted to an array before proceeding if required. Now URT is present in COLH of Table D so I want values related with URT from Table D in the retrieved collection.

How can achieve it in one query (with the query builder) using joins? The example of the collection received from the query will be something like this

{
'IDA': 1,
'IDB': 1,
'IDC': 1,
'IDD': 1
},
{
'IDA': 1,
'IDB': 1,
'IDC': 1,
'IDD': URT
},