9

I have a team schedule table that looks like this:

DBO.SCHEDULE

Game1_Time  |  Game1_Home_Team   | Game1_Away_Team 
===================================================
12:00:00    |         1          |         2

I want to replace the team values with their corresponding team that exists in another table:

DBO.TEAM

Team_Number  |  Team_Name
========================
    1        |  The Monsters
    2        |  Bug Bites

TRYING TO DO THIS: How do I replace the 1 & 2 in Schedule with "The Monsters" & "Bug Bites" in a query result? 

Game1_Time  |  Home Team         | Away Team 
===================================================
12:00:00    |  The Monsters      |  Bug Bites
Improve this question
3
  • 3
    JOIN Commented Aug 18, 2014 at 14:08
  • I'm having a hard time joining on two values in one row. Commented Aug 18, 2014 at 14:10
  • 2
    you can join tables multiple times using aliases. Commented Aug 18, 2014 at 14:11

1 Answer 1

Reset to default
16

basically just do two joins one for the home name and one for the away name.

SELECT 
     s.Game1_Time, 
     t.Team_Name as 'Home Team', 
     t1.Team_Name as 'Away Team'
FROM `SCHEDULE` s
JOIN `TEAM` t on t.Team_Number = s.Game1_Home_Team
JOIN `TEAM` t1 on t1.Team_Number = s.Game1_Away_Team

i added backticks because schedule is a keyword so just to not mess anything up you should use backtics on the tablename

DEMO

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.