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