Left join using a temporary column

I want to do left join on two tables but the second table doesn’t have required column. So a temporary column (concat of two column values in second table) is needed in order to do the left join.

select a.name, a.number, b.id, b.sub_num
from tableA a left join
     tableB b
     on a.number = (select concat(id,'-',Cast(sub_num as varchar)) as tempcol from tableB);

Obviously this shows error:

SUBQUERY_MULTIPLE_ROWS: Scalar sub-query has returned multiple rows.

But I don’t need this tempcol to show in the main query nor save it as new column in any tables here.

What is the better way to do left join on temporary column?

Answer

You don’t need a subquery for the join. Just use the expression:

select a.name, a.number, b.id, b.sub_num
from tableA a left join
     tableB b
     on a.number = concat(id, '-', sub_num);

If you like, you can use a subquery:

select . . .
from tableA a left join
     (select b.*, concat(id, '-', sub_num) as tempcol
      from tableB b
     ) b
     on a.number = b.tempcol;