Need help to write a query?

Question

 I have two tables as:

1) tblChartOfAccount

Fields:

AcctKey (bigint)  ==>PrimaryKey

AcctName(varchar)

and another table

2) tblJournalEntry

Fields:

TransID (bigint)

GLAcctKey (bigint) ==>ForiegnKey to tblChartOfAccount.AcctKey

ControlAcctKey (bigint) ==>ForiegnKey to tblChartOfAccount.AcctKey

Now I want to get data from second table tblJournalEntry in this form:

TransID, GLAcctName, ControlAcctName,

i.e I need in output both account's names instead of their keys. I am confused that I am able to write a join only on one PKcolum to one FKcolum.  But how can write join for two FKcolums to one PKcolumns?

Answer 1

Are you looking for the below Solution

Create Table tblChartOfAccount (AcctKey Bigint , AcctName Varchar(50))
Insert into tblChartOfAccount Values (101,'GLNAme')
Insert into tblChartOfAccount Values (102,'ControlNAme')
Create Table tblJournalEntry (TransID Bigint, GLAcctKey  Bigint , ControlAcctKey  Varchar(50))
Insert into tblJournalEntry Values (1011,101,102)
Select A.TransID,B.AcctName,C.AcctName From tblJournalEntry A
Left Join tblChartOfAccount B On A.GLAcctKey = B.AcctKey 
Left Join tblChartOfAccount C On A.ControlAcctKey = C.AcctKey

Answer 2

Select A.TransID, B.AcctName As GLAcctName, C.AcctName AS ControlAcctName
From tblJournalEntry A
Inner Join
tblChartOfAccount B
on A.GLAcctKey = B.AcctKey
Inner Join
tblChartOfAccount C
on A.ControlAcctKey = C.AcctKey

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!