Manage Learn to apply best practices and optimize your operations.

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
  JOIN TableB AS bp
    ON bp.ID = a.Parent_ID
  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.

This was last published in October 2008

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.