Showing names instead of IDs in a join

Question

I am trying to do a join and I cannot get to display what i want.

This is my current code.

SELECT
   Tournaments.TourneyDate, 
   Tournaments.TourneyLocation, 
   Tourney_Matches.OddLaneTeamID, 
   Tourney_Matches.EvenLaneTeamID
FROM
   Tournaments
INNER JOIN Tourney_Matches ON (Tournaments.TourneyID = Tourney_Matches.TourneyID);

This outputs Dates, Location, OddLaneIDs, and EvenLaneIDs.

Instead of the last two columns putting out raw IDs I need them to display the associated team names of the IDs.

They would have to reference to the Teams table and the column named TeamNames.

However, I dont know how to get this select statement to properly work.

SELECT
   Tournaments.TourneyDate, 
   Tournaments.TourneyLocation, 
   Teams.TeamName, 
   Teams.TeamName
FROM
   Tournaments
INNER JOIN Tourney_Matches ON (Tournaments.TourneyID=Tourney_Matches.TourneyID)
INNER JOIN Teams ON (Tourney_Matches.OddLaneTeamID=Teams.TeamID 
                     OR Tourney_Matches.EvenLaneTeamID=Teams.TeamID);

I tried using this statement but it displays the same names in both id columns.

This is what it is based off of.

https://i.stack.imgur.com/mIQX7.png

and the question is - Show tournament date, tournament location, odd lane team name, and even lane team name for each match in tournament

It must be done in a sub query.

You should show your tables. Give us an example from tables and write what you expect. — Kemal Güler, Jul 23 '17 at 01:46

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

EDIT

If it must be done in a sub query, then:

SELECT
   Tournaments.TourneyDate, 
   Tournaments.TourneyLocation, 
   (SELECT TeamName FROM Teams WHERE TeamID = Tourney_Matches.OddLaneTeamID) AS 'Odd Lane Team', 
   (SELECT TeamName FROM Teams WHERE TeamID = Tourney_Matches.EvenLaneTeamID) AS 'Even Lane Team' 
FROM
   Tournaments
   INNER JOIN Tourney_Matches ON (Tournaments.TourneyID=Tourney_Matches.TourneyID)

OLD ANSWER

Try this (Source of insipration):

SELECT
   Tournaments.TourneyDate, 
   Tournaments.TourneyLocation, 
   OddLaneTeams.TeamName, 
   EvenLaneTeams.TeamName 
FROM
   Tournaments
INNER JOIN Tourney_Matches ON (Tournaments.TourneyID=Tourney_Matches.TourneyID)
INNER JOIN Teams AS OddLaneTeams ON Tourney_Matches.OddLaneTeamID = OddLaneTeams.TeamID
INNER JOIN Teams AS AwayLaneTeams ON Tourney_Matches.EvenLaneTeamID = EvenLaneTeams.TeamID

Showing names instead of IDs in a join

1 Answers1

EDIT

OLD ANSWER