Joining table mysql -- without double iteration

Question

Let me start by saying this should be a relatively simple problem which is / was made unnecessary complicated by bad Database design (not by me) that said im also no expert in mysql.

Consider the following

Table Schedule

Note how the columns homeID and visitorID contains the names of the teams and not the actual teamID's

In a bid to fix this I created a new table with columns containing teamID AND teamName as can be seen by below image.

Table Teams

My Problem(s)

I must get the teamID from table Teams for BOTH home team AND away team

So I created the Teams table and this simple script:

SELECT schedule.*, teams.* FROM schedule JOIN teams ON schedule.homeID = teams.teamName OR schedule.visitorID = teams.teamName WHERE schedule.gameID = 411 LIMIT 1 #added Limit1 else the code generates to rows

Output of mysql Script

Limit 1

Notice above how teamID is only generated for 1 team with Limit 1

No Limit Statement (Double Iteration)

Notice above how teamID can get retrieved for BOTH teams. Problem is its doing a double iteration.

TLDR; The above presents the following problems

1. Firstly the script will generate two outputs one for home team and once for away team. As to be expected however I cant have that.

2. As a workaround to Problem number 1 -- I added Limit 1 the problem I get with Limit though is that it only gives back a single teamID (as to be expected, I guess)

Question

How can I get BOTH teamID's from table teams with a single iteration? Hope this make sense....

Extra

A demo of application with hard coded team names looks like this (just to give an idea of what they are trying to achieve)

Answer 1

Sounds like you want to join teams twice to schedule.

SELECT s.*,
       th.*,
       ta.*
       FROM schedule s
            INNER JOIN teams th
                       ON s.homeid = th.teamname
            INNER JOIN teams ta
                       ON s.visitorid = ta.teamname 
       WHERE s.gameid = 411;

Answer 2

I guess that you want to show both team in one row instead of two rows.
If yes, then you need to join the table teams twice.

Consider this demo: http://www.sqlfiddle.com/#!9/bb5e61/1

This join will collect both teams into one row:

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
JOIN Teams t1 ON s.homeId = t1.teamName
JOIN Teams t2 ON s.visitorId = t2.teamName;

| id | homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|--------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 | Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |

However you can also consider LEFT joins instead on INNER joins, which will work in a case where there is no relevant data in the TEAMS table:

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
LEFT JOIN Teams t1 ON s.homeId = t1.teamName
LEFT JOIN Teams t2 ON s.visitorId = t2.teamName;

| id |   homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|----------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 |   Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |
|  3 | Ya Majka |    Poland |        (null) |          (null) |          (null) |                1 |                 PL |             Poland |
|  2 | Ya Majka |   Rossija |        (null) |          (null) |          (null) |           (null) |             (null) |             (null) |

---

Here are the scripts that make up the tables from the examples

CREATE TABLE Schedule(
  id int,   homeId varchar(20),visitorId varchar(20)
);

INSERT INTO Schedule VALUES
(1, 'Poland', 'Colombia' ),(2,'Ya Majka','Rossija'), 
(3,'Ya Majka','Poland');

CREATE TABLE Teams( 
  teamId int, teamCode varchar(10), teamName varchar(20)
);

INSERT INTO Teams VALUES
(1, 'PL', 'Poland' ),(2,'CO','Colombia'),(3,'US','United States');