SQL: join (help)

Question

The task is: to join two tables, but not just by columns. Suppose there are tables t1 and t2. In t1, the columns {id, namet1}, t2 - {id, id_t1_with000, namet2}.

Example

t1.id: 
1 
2 
3 
.... 

Example

t2.id_t1_with000: 
100 
200 
300 
....

Problem: how to connect the tables t1 and t2 with t1.id and t2.id_t1_with000.

I thought that it is possible so:

SELECT * FROM t1 JOIN t2 on t1.id = t2.id_t1_with000 [3]

How can such a connection be made? How about this google? nothing I could not find.

Answer 1

You could join the tables using a substring.

example:

select * from table1 t1
join table2 t2 on substring(cast(t2.id as varchar), 1, len(t2.id)-2) = t1.id 

With help of the SubString you can simply extract the ID from all those zero's.

"len(t2.id)-2" because you have 2 zero's behind your ID. This only works if your id is always filled with the same amount of zero's