Query to call 2 foreign key on 1 primary column-CodePudding

I have a SQL Server table on which I insert account wise data like A/C# this is varchar field and I have id as primary key.

In other chargestable account# is stored in 2 different columns as interest A/C# and profit A/C#; the A/C# is different in the row

The customer retrieves the charges data and account number need to display.

As both are different fields should I do foreign key for both fields to primary table

Please guide with the optimum way.

    Account Table
    ----------------------
   id | AccountNumber
    -----------------
    1 | 4321
    --------------
    2 | 5432
    -------------- 
    3 | 6543
    -------------- 
    4 | 7654
    -------------- 
    5 | 8765
    --------------

Charges Table
        ----------------------
       id | interestA/c# |Profit A/c#
        -----------------------------
        1 | 2            |3
        -----------------------------
        2 | 3            |4
        -----------------------------
        3 | 4            |5
        -----------------------------
        4 | 5            |1
        -----------------------------

Output: Select * from Charges;

---------------
 1 | 5432 |6543
 ---------------
 2 | 6543 |7654
 ---------------
 3 | 7654 |8765
 ---------------
 4 | 8765 |4321
 ---------------

CodePudding user response：

Join the Charges table to the Account table, twice:

SELECT c.id, a1.AccountNumber, a2.AccountNumber
FROM Charges c
INNER JOIN Account a1
    ON a1.id = c.[interestA/c#]
INNER JOIN Account a2
    ON a2.id = c.[Profit A/c#]
ORDER BY c.id;