OraFAQ Forum: SQL & PL/SQL » Table joining with two columns sharing a foreign key

Home » SQL & PL/SQL » SQL & PL/SQL » Table joining with two columns sharing a foreign key

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table joining with two columns sharing a foreign key [message #683028]

Thu, 26 November 2020 21:25

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.

Report message to a moderator

Re: Table joining with two columns sharing a foreign key [message #683029 is a reply to message #683028]

Fri, 27 November 2020 00:48

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: Table joining with two columns sharing a foreign key [message #683030 is a reply to message #683028]

Fri, 27 November 2020 05:43

Solomon Yakobson
Messages: 3212
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 10:54:21 CDT 2023