Search code examples
sqlsql-server

SQL Server conditional join on column value

I have two tables - Table A and Table B

Table A

Value      Value2      Number
-------    ------      ------
AA          1          138
AB          2          234
AC          3          897
BA          4          274
BB          5          391
CA          4          994
CA          5          287

Table B

Value      Value2      Number
-------    ------      ------
AA          1          458
AB          2          434
AC          3          557
CA          4          564

I am joining Table A and Table B with respect to Value and Value2 column.

SELECT
     * 
FROM
   Table A TA 
JOIN
   Table B TB ON 
   TA.Value = TB.Value 
   AND TA.Value2 = TB.Value2

Table A and Table B does not have all the matching value, for example value column of Table A have BA and BB which is not matching in Table B value column, so I need to replace BA and BB with CA and join it.

I started writing the script but wrote a long script.

I used except to get all the value column from TABLE A not matching TABLE B , and put this into a temp table. Then loop and replace the BA / BB column with CA column. But I think there should be a easy steps than this which I struggle to find.

Any suggestions please.

Solution

  • See example

    create table table_A(Value varchar(2), Value2 int, Number int);
insert into table_A values
 ('AA',          1,          138)
,('AB',          2,          234)
,('AC',          3,          897)
,('BA',          4,          274)
,('BB',          5,          391)
,('CA',          4,          994)
,('CA',          5,          287)
;
create table table_B(Value varchar(2), Value2 int, Number int);
insert into table_B values
 ('AA',          1,          458)
,('AB',          2,          434)
,('AC',          3,          557)
,('CA',          4,          564)
;
select * from table_A;
select * from table_B;
    Value Value2 Number
    AA 1 138
    AB 2 234
    AC 3 897
    BA 4 274
    BB 5 391
    CA 4 994
    CA 5 287
    Value Value2 Number
    AA 1 458
    AB 2 434
    AC 3 557
    CA 4 564 
    select * 
from table_A a
left join table_B b on a.value=b.value
   and a.value2=b.value2
left join table_B c on b.value is null and c.value='CA'
    Value Value2 Number Value Value2 Number Value Value2 Number
    AA 1 138 AA 1 458 null null null
    AB 2 234 AB 2 434 null null null
    AC 3 897 AC 3 557 null null null
    BA 4 274 null null null CA 4 564
    BB 5 391 null null null CA 4 564
    CA 4 994 CA 4 564 null null null
    CA 5 287 null null null CA 4 564 
    select a.value valueA
  ,coalesce(b.value,c.value) value
  ,coalesce(b.value2,c.value2) value2
  ,coalesce(a.number,c.number) numberA
  ,coalesce(b.number,c.number) numberB
from table_A a
left join table_B b on a.value=b.value
   and a.value2=b.value2
left join table_B c on b.value is null and c.value='CA'
    valueA value value2 numberA numberB
    AA AA 1 138 458
    AB AB 2 234 434
    AC AC 3 897 557
    BA CA 4 274 564
    BB CA 4 391 564
    CA CA 4 994 564
    CA CA 4 287 564

    fiddle