Home > Net >  DB2 - coalesce - join
DB2 - coalesce - join

Time:11-20

Given 3 tables. How can I choose the right join if two cases were possible.

Table1

Article CustomerID-extern CustomerID-intern
table A13590
chair B23877

Table2

CustomerID-extern CustomerID-intern
A13590 BX13590

Table3

CustomerID-intern Customername CustomerAddress
BX13590 Microsoft SiliconValley
B23877 Amazon New York

In the result I need the CustomerID-intern with the assigned address of it. In Table1 there can only be filled 1 field either CustomerID-extern or CustomerID-intern.

Article CustomerID-intern Customername CustomerAddress
table BX13590 Microsoft SiliconValley
chair B23877 Amazon New York

Thank you.

CodePudding user response:

You can use two left outer joins to match rows in the third table accordind to the direct codes or the translation codes in the second table. For example:

select a.Article, c.*  
from table1 a
left join table2 b on a.CustomerID_extern = b.CustomerID_extern
left join table2 c on c.CustomerID_intern = a.CustomerID_intern
                   or c.CustomerID_intern = b.CustomerID_intern
  • Related