phalondon phalondon - 2 months ago 5
SQL Question

How can join 2 Table when Key are in different columns

i have 2 Tables: accounthierarchy and accountvaluetotal.

the link between 2 Tables is account number. i want to join the table based on account number. But the account number of table "account hierarchy " is on different Level (column).

Can you please help me how to do it? Thanks

CREATE TABLE [dbo].[accounthierarchy](
[ID] [int] NULL,
[level1] [int] NULL,
[level2] [int] NULL,
[level3] [int] NULL,
[level4] [int] NULL,
[level5] [int] NULL)

INSERT INTO [dbo].[accounthierarchy] (ID,level1,Level2,level3,level4,level5)

CREATE TABLE [dbo].[accountvaluetotal](
[accountnumber] [int] NULL,
[values] [int] NULL

insert into [dbo].[accountvaluetotal]


On the basis that the account number is the 'last' value in the accounthierarchy table;

SELECT ID, COALESCE(ac.level5,ac.level4,ac.level3,ac.level2,ac.level1) as AccountNumber
from [accounthierarchy] ac

Will then give you the the account number, to which you can then do a standard join

COALESCE gives the first non-null value from a list of values, so by going from level 5 to level 1, it will return whichever valid value it arrives at first.