I have a table for costing of my application which contains 2 AccountId per each row as below:
tbl_CostType
| id | DebitAccountId | CreditAccountId |
|---|---|---|
| 1 | 20 | 22 |
tbl_Accounts
| id | joinAccountId |
|---|---|
| 20 | j1 |
| 22 | j2 |
tbl_JoinAccounts
| id | Name |
|---|---|
| j1 | David |
| j2 | Robert |
How can i get following result with a query ?
| debitAccountId | debitName | creditAccountId | creditName |
|---|---|---|---|
| 20 | David | 22 | Robert |
SELECT costType.DebitAccountId, JoinAccount.Name as debitName, costype.creditAccountId, JointAccount.Name as creditName
FROM costype INNER JOIN
Account ON costype.creditAccountId = Account.Id INNER JOIN
JointAccounts ON Accounts.JointAccountId = JointAccounts.Id
But this query return a record with same value for both **creditName **and debitName
I would appreciate any help