Join based on the value to different table

Question

Hi everyone I have a table TableC which saves primary key values from two different tables TableA and TableB. because they are primary keys from two tables I could have end up with duplicates in that tableC so when Storing values I prefixed the Primary keys with a short text to differentiate that which value is coming from which table.

Now I would like to Join this tableC with TableA and TableB to get the data from tableA and TableB

TableC : 

ID_Column 
1A
1B
2A

TableA: 

ID_Column   |   Data
1           |   data A 1
2           |   data A 2
3           |   data A 3

TableB: 

ID_Column   |   Data
1           |   data B 1
2           |   data B 2
3           |   data B 3

This is what I have been trying to do

select C.ID_Column, data
from tableC C
      inner join tableA A
on A.ID_Column = left(C.ID_Column, 1)
      inner join tableB B
on B.ID_Column = left(C.ID_Column, 1)

this will return data from both tables I want to return data from table b when ID_Column has B in the end and want to return data from tableA when ID_Column has A in the end

Thank you in advance.

Answer 1

Well, TableC should really have at least 2 columns, one for the id and one to identify to which table it belongs. Anyway, this could be done this way:

SELECT  C.ID_Column,
        ISNULL(A.Data,B.Data) Data
FROM TableC C
LEFT JOIN TableA A
    ON LEFT(C.ID_Column,LEN(C.ID_Column)-1) = A.ID_Column
    AND RIGHT(C.ID_Column,1) = 'A'
LEFT JOIN TableB B
    ON LEFT(C.ID_Column,LEN(C.ID_Column)-1) = B.ID_Column
    AND RIGHT(C.ID_Column,1) = 'B'

The results are:

╔══════════╦══════════╗
║ D_COLUMN ║   DATA   ║
╠══════════╬══════════╣
║ 1A       ║ data A 1 ║
║ 1B       ║ data B 1 ║
║ 2A       ║ data A 2 ║
╚══════════╩══════════╝

And here is a demo for you to try.