CODEWITHSUNDEEP
sqlsql-serversql-server-2012
hana
hana

Reputation: 121

SQL join with different conditions

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.

enter image description here

enter image description here

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.

Upvotes: 1

Views: 145

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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)

Upvotes: 1

Related Questions