How to write an SQL query for two foreign keys to the same table
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
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.
Oracle White Papers: Fusion Middleware