1

Hi I have two tables one named Team and the other named Schedule.

The Schedule table has the following attributes: id, away, home, date... where away and home are foreign keys to the Team table.

The Team table has the following attributes: name and id.

My goal is to join Team and Schedule so that schedule.id, away, awayTeamName, home, homeTeamName, date are all in the same query. This is what i have so far

SELECT  `GAME_ID` ,  `AWAY` , TEAM.NAME AS awayName,  `HOME`,  `LEVEL` , 
`LOCATION` ,  `DATE` 
FROM SCHEDULE
INNER JOIN TEAM ON SCHEDULE.AWAY = TEAM.TEAM_ID
OR SCHEDULE.HOME = TEAM.TEAM_ID

and the output i get is something like this

GAME_ID | AWAY | awayName | HOME | LEVEL | LOCATION | DATE
1       | 2    |  name1   | 3    |varsity| @home    | 06/22/2015

But I want it to look like this

GAME_ID | AWAY | awayName | HOME | homeName| LEVEL | LOCATION | DATE
1       | 2    |  name2   | 3    | name3   |varsity| @home    | 06/22/2015

If anybody can help i would appreciate it

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
Alan
  • 87
  • 1
  • 9

2 Answers2

0

You can have more than one join.

SELECT  SCHEDULE.GAME_ID , away.AWAY, away.NAME AS awayName, home.HOME , home.NAME AS homeName, LEVEL , 
LOCATION , DATE 
FROM SCHEDULE
INNER JOIN TEAM as away ON SCHEDULE.AWAY = TEAM.TEAM_ID INNER JOIN TEAM as home ON
SCHEDULE.HOME = TEAM.TEAM_ID

It will be something like this.

HadeS
  • 2,020
  • 19
  • 36
Otávio Ribeiro
  • 16
  • 4
0
 INNER JOIN TEAM as home ON SCHEDULE.HOME = TEAM.TEAM_ID

add this to the end of your query,this gives you two inner joins which is perfectly legal.

you should be able to find this useful whenever you have more than two tables to deal with in the future.

inner joins on more than 2 table

Community
  • 1
  • 1
pycod333
  • 764
  • 2
  • 7
  • 16