Depending on column value, query different tables

I have table A where each IDENTIFIER value is unique. Depending on whether its CC or DC, I need to join it with different tables, for example if its “CC_” I need to map it to table B and if its “DC_” I need to map it to table C.

TABLE A:

ID | IDENTIFIER
1  | CC_1234567
2  | DC_9494949

TABLE B:

ID| SOURCE.  |INSTRUMENT
7 | 1234567. | 545454

TABLE C:

ID  |  SOURCE  | INSTRUMENT
8   |. 9494949 |. 63636363

I want the result to look like this:

IDENTIFIER | INSTRUMENT
CC_1234567 | 545454
DC_9494949 | 63636363

First of all, how could I just get “1234567” from “CC_1234567” and second when writing a join query, I would get other columns in the final resulting table, such as B.INSTRUMENT, C.INSTRUMENT. Each IDENTIFIER in table A only maps to either table B OR table C.

Select A.IDENTIFIER, B.INSTRUMENT, C.INSTRUMENT 
from table.A 
inner join table.A on A.IDENTIFIER=B.INSTRUMENT AND A.IDENTIFIER=C.INSTRUMENT

Answer

You can left join twice, and then use coalesce() in the select clause:

select a.identifier, coalesce(b.instrument, c.instrument) instrument
from tablea
left join tableb b on a.identifier = concat('CC_', b.source)
left join tablec c on a.identifier = concat('DC_', c.source)

If there are matches in both tables, the above query gives priority to tableb.

If you want to exclude rows that have no match in either tables, then you can add the following where clause:

where a.source is not null or b.source is not null