SQL Statement help

[mydrive22]

This is probably an easy one. I am fairly new to this.

Here is what I have

I am working with three tables right now
a Teams table which lists Team names and their ID's

In another table I have games listed with Visiting teams and Home teams IDs, as well gameDate, Status, etc.

I am working on a schedule page for a website using PHP. I am pulling all the informatin from the games table to use on the scheule but I need to replace the Team ID's with Team Names. Obviously You do this with a Join, and I can get it to work for the visiting team OR the home team one or the other. I cannot get it to grab BOTH. Any Ideas?

 

[feherke]

Hi

Use the team table twice :

[b]select[/b]
*
[b]from[/b] game g
[b]inner join[/b] team v [b]on[/b] h.id=g.visiting_team_id
[b]inner join[/b] team h [b]on[/b] h.id=g.home_team_id

Feherke.

http://rootshell.be/~feherke/

 

[mydrive22]

Thans for your help.

I couldnt quite get it to work so I used some nested Select statements

SELECT GAME.GAME_ID
,GAME.DATE
,GAME.GAME_STATUS
,GAME.HOME_TEAM_ID
,(SELECT TEAMs.TEAM_NAME FROM TEAMs WHERE GAME.HOME_TEAM_ID = TEAMs.TEAM_ID) AS HOME_TEAM
,GAME.VISITINGTEAMID
,(SELECT TEAMs.TEAM_NAME FROM TEAMs WHERE GAME.VISITING_TEAM_ID = TEAMs.TEAM_ID) AS VISITING_TEAM
,GAME.HOME_SCORE
,GAME.VISITING_SCORE
FROM GAMES_TBL GAMES

 

[feherke]

Hi

Yes, that is also a way to do it, but is the slowest. Avoid [tt]select[/tt]s in the field list of another [tt]select[/tt].

Feherke.

http://rootshell.be/~feherke/