CODEWITHSUNDEEP
sqlsql-servert-sqljoin
ojorma
ojorma

Reputation: 385

Inner joins with 2 foreign keys to one primary key

I have a table called branch (branchid, branchname) and another table called transfer transfer(tranferid, sourcebranch, destinationbranch)

both sourcebranch and destinationbranch are Fk to the branchid of of branch table. I need to show a query that looks like this

Tranferid Source Destination 4 uk us

but all I can get is something like this

Tranferid Source Destinationid 4 uk 3

query sample

select tranferid, branch.branchname, transfer.destinationbranch from transfer inner join branch on branch.branchid == transfer.sourcebranch

How do I get the destination branch to show. CTE on my mind

Upvotes: 4

Views: 6134

Answers (1)

John Woo
John Woo

Reputation: 263693

You need to join table branch on table transfer twice so you can get the value for each column.

SELECT  a.*, 
        b.branchName AS sourceBranchName,
        c.branchName AS destinationBranchName
FROM    transfer a
        INNER JOIN branch b
            ON a.sourcebranch = b.branchID
        INNER JOIN branch c
            ON a.destinationbranch = c.branchID

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 5

Related Questions