SQL table join on Oracle

Transact-SQL
1

I have a table A whose primary key is a foreign key in two columns on table B. I am trying to join both tables so as to get the output as one table as names are in table B and IDs are in table A.
I tried using the query below but it works for only one column and doesn't for the other one

"select team.team_long_name home_team, match.home_team_api_id, team.team_long_name away_team, match.away_team_api_id
from match
join team
on match.home_team_api_id = team.team_api_id
join team
on match.away_team_api_id = team.team_api_id"

The output this gives is the home team ID and the right Home team name, the Away team id but the wrong away team name. It gives the same output as the home team name.

Kindly assist

2

You need to distinguish the two table table joins from each other, using table aliases, like this:

select teamh.team_long_name home_team, match.home_team_api_id, teama.team_long_name away_team, match.away_team_api_id
from match
join team teamh
on match.home_team_api_id = teamh.team_api_id
join team teama
on match.away_team_api_id = teama.team_api_id
1 Like
3

Many Many thanks Scott. This worked

4

You're very very welcome.