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
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading