SQL join with different conditions-CodePudding

I want to inner join TableA and TableB using columns invoice and nr. the column nr has different formats on both tables in TableA. Column nr always has three positions or an empty value. in TableB, Column nr has 1 or 2 positions.

For example, if we check for invoice= 322 and invoice= 234 on both tables. I must fill in "0" or "00" on TableB to equal the number of positions.

and for TableA, when nr is a null or empty value, I want to replace it by 0 For example, if we check invoice=123 and invoice=567 on both tables.

I appreciate any help you can provide.

CodePudding user response：

TableA's nr column is probably a VARCHAR instead of a number type.
So CAST or CONVERT it to the same type as TableB.nr (probably INT)

And use COALESCE or ISNULL to change the NULL's to 0

SELECT 
  a.invoice
, b.nr
, a.name
FROM TableA a
INNER JOIN TableB b
  ON b.invoice = a.invoice 
 AND b.nr = ISNULL(CAST(a.nr AS INT), 0)