Joining 2 tables based on Dynamic criteria

Question

Hoping you can help me. I have 2 Tables (Table PS & Table A).

What i am trying is the following ;

Depending on the ACCOUNT_NUMBER & 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 & 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 & 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 & 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 & support

Answer 1

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') > 0)
                         OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0)
                         OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > 0) )

The rest is trivial.

Answer 2

So in full, you could have something like this:

SELECT NVL(#a.account_number,#PS.ACCOUNT_CODE__C) account_number
,      NVL(#a.id,#ps.I) id
,      DECODE(#ps.id, NULL, 'N', 'Y') table_PS
,      DECODE(#a.id, NULL, 'N', 'Y') table_a 
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') > 0)
                         OR (#A.ID = #PS.T AND INSTR(#A.Id_Type, 'T') > 0)
                         OR (#A.ID = #PS.C AND INSTR(#A.Id_Type, 'C') > 0) )

(assuming that if id does not exist in table A, you report id in column I of table PS)