How to write an SQL query for two foreign keys to the same table
A SQL user asks how to write a query for two foreign keys to the same table. Read SQL expert Rudy Limeback's answer here.
How can we retrieve data from two tables, when one of the tables has two foreign keys pointing to the same primary key of another table? For example, TableA has two columns, Parent_ID and Child_ID. TableB has two columns, ID and Name. How can we retrieve data and show it as Parent_ID, Parent_Name, Child_ID, Child_Name.
Just join to the table twice. To accomplish this, you must use table aliases.
SELECT a.Parent_ID
, bp.Name AS Parent_Name
, a.Child_ID
, bc.Name AS Child_Name
FROM TableA AS a
INNER
JOIN TableB AS bp
ON bp.ID = a.Parent_ID
INNER
JOIN TableB AS bc
ON bc.ID = a.Child_ID
Notice how TableB occurs twice in the query. This means we must use an alias for each occurrence, and also use the appropriate alias to qualify the columns in the SELECT clause. And since the columns from the two instances of TableB have the same name, it is also common—but not required—to assign column aliases in the SELECT clause.
Dig Deeper on Oracle development languages
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments