Joining 2 tables based on Dynamic criteria Hoping you can help me. I have 2 Tables (Table PS &amp; Table A). 

What i am trying is the following ;

Depending on the ACCOUNT_NUMBER &amp; Id_Type of table A, search the Table PS on the following conditions ACCOUNT_CODE__C and  I or T or C. 

For example :

In the scenario below for Table A for ACCOUNT_NUMBER = '1234' the Id_Type = 'I', therefore, i want it to to search the PS table for ACCOUNT_CODE__C '1234' and against column I

If in future scenario below the for table A for ACCOUNT_NUMBER = '1234' and has Id_Type of = 'T', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column T

If in future scenario below the for table A for ACCOUNT_NUMBER  = '1234' the Id_Type = 'C', therefore, i want it to to search the Ps table for ACCOUNT_CODE__C '1234' and against column C

There may be a scenario where Table A ACCOUNT_NUMBER = '1234' the Id_Type could have a mixture of "I", "T" or "C".

Where it matches then provide the join. Where it doesn't match enter a new line item and populate the value a

In the expected outcome below, you can see that the following Id ("LU0336376248 &amp; LU0430493568") from the PS table are not present in Table A. We can also see that "LU0513029156" inst in present in Table PS. 

Table PS &amp; Data

create table #PS
(
ACCOUNT_CODE__C nvarchar(80),
ID nvarchar(80),
I nvarchar(80),
T nvarchar(80),
C nvarchar(80)
);


INSERT INTO #PS
VALUES
('1234', 'a1vf1000001icrBAAQ','LU0430492750', 'TESTCAEU LX', 'L5784E347'),
('1234', 'a1vf1000001icrCAAQ','LU1162086109', 'TESTGIHA LX', ''),
('1234', 'a1vf1000001icrDAAQ','LU0336376248', 'TESTGEUD LX','L57838408'),
('1234', 'a1vf1000001icrEAAQ','LU0430493568', 'TESTGBCH LX', 'L5784E420');


Table PS Result : 

select * from #ps

ACCOUNT_CODE__C ID  I   T   C
1234    a1vf1000001icrBAAQ  LU0430492750    TESTCAEU LX L5784E347
1234    a1vf1000001icrCAAQ  LU1162086109    TESTGIHA LX 
1234    a1vf1000001icrDAAQ  LU0336376248    TESTGEUD LX L57838408
1234    a1vf1000001icrEAAQ  LU0430493568    TESTGBCH LX L5784E420


Table A &amp; Data

create table #a
(
ACCOUNT_NUMBER nvarchar(80),
ID nvarchar(80),
Id_Type nvarchar(80)
);


INSERT INTO #a
VALUES
('1234', 'LU0430492750', 'I' ),
('1234', 'LU1162086109', 'I' ),
('1234', 'LU0513029156', 'I' );


Table A Result :

select * from #a

ACCOUNT_NUMBER  ID  Id_Type
1234    LU0430492750    I
1234    LU1162086109    I
1234    LU0513029156    I


Expect Outcome When tables Joined

ACCOUNT_NUMBER  ID        Table PS   Table A 
1234    LU0430492750       Y           Y
1234    LU1162086109       Y           Y
1234    LU0336376248       Y           N
1234    LU0430493568       Y           N
1234    LU0513029156       N           Y


Looking forward to your help &amp; support
Joining (or the expression in SELECT) looks similar to:

FROM #A 
FULL OUTER JOIN #PS ON #A.ACCOUNT_NUMBER = #PS.ACCOUNT_CODE__C 
                   AND (    (#A.ID = #PS.I AND INSTR(#A.Id_Type, 'I') &gt; 0)
                         OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') &gt; 0)
                         OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') &gt; 0) )


The rest is trivial.