How to join 2 independent tables

Question

I'm using SQL there are 2 independent tables .1 table one provides the codes and id's the data looks like

Code    ID
AUD     WH1
CND     WH1

.2 tables has the long description for these codes which looks like

colval            Description

AUD|WH1         Australia records
CND|WH1         Canada records

the column 'colval' is concatenation of columns 'code' and 'id' with delimiter '|' of #table 1. how to join these tables to get the result like

Code      Description
AUD      Australia records
CND      Canada records

Answer 1

You can use a join with concatenation logic:

select t1.*, t2.description
from table1 t1 join
     table2 t2
     on t2.colv = t1.code || '|' || t1.id;

In SQL Server:

select t1.*, t2.description
from table1 t1 join
     table2 t2
     on t2.colv = concat(t1.code, '|', t1.id);

Unfortunately, Oracle supports CONCAT() but with only two arguments. So for code that works on both databases:

select t1.*, t2.description
from table1 t1 join
     table2 t2
     on t2.colv = concat(concat(t1.code, '|'), t1.id);