Home » SQL & PL/SQL » SQL & PL/SQL » Table joining with two columns sharing a foreign key
Table joining with two columns sharing a foreign key [message #683028] Thu, 26 November 2020 21:25 Go to next message
richard_blaq
Messages: 1
Registered: November 2020
Junior Member
I am learning oracle SQL however I am finding it difficult to join two peculiar tables. I have a table A and a table B.

Table A's primary key (lets call this team_id) is a foreign key in table B but this foreign key is broken into two columns (B.home_team_id and B.away_team_id).

I would like to join this two tables together showing the seperate columns in table B.
Re: Table joining with two columns sharing a foreign key [message #683029 is a reply to message #683028] Fri, 27 November 2020 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Have a look at join.
Re: Table joining with two columns sharing a foreign key [message #683030 is a reply to message #683028] Fri, 27 November 2020 05:43 Go to previous message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
So join A twice:

SELECT  B.*,
        HOME_TEAM.*,
        AWAY_TEAM.*
  FROM  B,
        A HOME_TEAM,
        A AWAY_TEAM
  WHERE B.HOME_TEAM_ID = HOME_TEAM.TEAM_ID
    AND B.AWAY_TEAM_ID = AWAY_TEAM.TEAM_ID
/
SY.

[Updated on: Fri, 27 November 2020 05:44]

Report message to a moderator

Previous Topic: One row for multiple rows based on a condition
Next Topic: Creating Procedure (2 merged)
Goto Forum:
  


Current Time: Sun May 16 13:52:29 CDT 2021