Linking two columns to the same foreign key

[MargateSteve]

I have a table that holds a set of matches in a football (soccer) league. On a teams individual page I want to show just their matches and am very close to getting it right but just cannot get there!

 

I am using 2 tables for this....

teams

team_idteam_name

1TEAM1

2TEAM2

3TEAM3

4TEAM4

 

and

 

all_games

game_idhome_teamhome_scoreaway_teamaway_score

11 220

23 041

34 211

42 030

Both 'home_team' and 'away_team' are foreign keys to teams.team_id.

 

The query that I have used to almost get it working is

SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND all_games.home_team=teams.team_id

 

And the html is

     

   
<tr>
        <td>all_games_id</td>
        <td>home_team</td>
        <td>home_goals</td>
        <td>away_team</td>
        <td>away_goals</td>
</tr>
<tr>
        <td><?php echo $row_all_games['all_games_id']; ?></td>
        <td><?php echo $row_all_games['team_name']; ?></td> 
        <td><?php echo $row_all_games['home_goals']; ?></td>
        <td><?php echo $row_all_games['team_name']; ?></td>
        <td><?php echo $row_all_games['away_goals']; ?></td>
        </tr>      
     

 

This pulls the correct games into the page but will show the 'home_team.team_name' as both 'home_team' and 'away_team'. For example the page for TEAM1 shows up as

match_idhome_teamhome_scoreaway_teamaway_score

1TEAM12TEAM10

3TEAM42TEAM41

 

What it should show is

match_idhome_teamhome_scoreaway_teamaway_score

1TEAM12TEAM20

3TEAM42TEAM11

 

Obviously in the current query there is nothing to show that the 'away_team' is also equal to 'teams.team_id'. I have tried this query

SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND (all_games.home_team=teams.team_id OR all_games.away_team=teams.team_id)

But this gives the exact opposite result with just the 'away_team.team_name' showing up as 'home_team' and 'away_team'

match_idhome_teamhome_scoreaway_teamaway_score

1TEAM22TEAM20

3TEAM12TEAM11

 

while

 

SELECT * FROM  all_games, teams WHERE '%s' IN (home_team, away_team )  AND (all_games.home_team=teams.team_id AND all_games.away_team=teams.team_id)

throws up an empty query.

 

Does anyone have any suggestions on how this can be done? I have exhausted my patience with Google simply as I do not know what the correct search term is.

 

 

As an aside, when I do get it working, I want the format on the page to be slightly different in that instead of showing both home_team and away_team names I would want it to show "H" if the team in question is the 'home_team' and "A" if it is the 'away_ team' plus the name of the team that they played and whether the 'home_score' or 'away_score' belonged to them, as well as whether they won, drew or lost the game. So, staying with the page for TEAM1 it would show as

MATCH IDHOME or AWAYOPPONENTW/D/LSCORE FORSCORE AGAINST

1HTEAM2Won20

3ATEAM4Lost12

 

I am currently planning on doing this with IF statements in the html, something along these lines

if home_team = %s echo "Home" else echo "Away"

and

if home_team = %s AND home_score>away_score echo "Won"
else if away_team = %s and away_score>home_score echo "Won"
else if home_score= away_score echo "Drew"
else echo "Lost

 

If anyone can think of a better way to do this ( I am naturally assuming that it would not work in the way I intend!)I would be extremely greatful too!

 

Thanks in advance for any advice, pointers or offers to write the whole code!!!

Steve

 

[DavidAM]

You have to join to the teams table twice, using a different alias for each occurrence of the table:

 

SELECT HT.team_name as HomeTeam, VT.team_name as AwayTeam, AG.home_score, AG.away_score
FROM all_games AS AG JOIN teams as HT ON AG.home_team = HT.team_id
JOIN teams AS VT ON AG.away_team = VT.team_id

[MargateSteve]

Thanks for that David but I am afraid it has gone way over my head!!

 

I replaced the query with your one but all that was returned was

Unknown column 'AG.home_score' in 'field list'

 

I assume that some of that had to be changed to suit my needs but as I am very new to all of this I am not sure what to change! Based on the theory that "AG.home_score" is 'table.field' I changed it to "all_games.home_score" but got

Unknown table 'all_games' in field list

 

As I am on a steep learning curve with this (only started in earnest in June) would it be possible to give me an explanation of what the query is doing. Hopefully with a bit of understanding I could work out what to change. Also, if I understand it will save me from asking the same question when I hit a future (and there will be) stumbling block!

 

Thanks in advance

Steve

 

[DavidAM]

SELECT HT.team_name as HomeTeam, VT.team_name as AwayTeam, AG.home_score, AG.away_score
FROM all_games AS AG JOIN teams as HT ON AG.home_team = HT.team_id
JOIN teams AS VT ON AG.away_team = VT.team_id

 

Sorry, I have a habit of "aliasing" tables so there is less to type; plus, we had to so we could use the same table twice.  I got the table and column names from your post, so they should be correct, I think.

 

You are correct that AG.home_score is of the form table.column. But you will notice in the FROM clause we say we are going to refer to the all_games table AS AG.  The 'AG' here is called an alias.  Anywhere we use AG to qualify a column, we are referring to the all_games table.  We can't use 'all_games' now to qualify a column name, because we said we are going to call it AG. You could take out 'AS AG' and change all of the 'AG.' references to 'all_games.' and the query will be the same.

 

We can't do that with the other two tables though. Since we need two instances of of the teams table, we have to use an alias for at least one of them.

[MargateSteve]

Firstly, thanks for your patience with this. Often on forums the response to someone with as little knowledge as I have is to tell the asker to look at a page at dev.mysql.com or php.net that explains it in overly technical terms before coming back and asking the question properly. It is refreshing to find somewhere that gives help to noobs!

 

Secondly, you would not believe the length that this post was originally because I still could not get it to work. I put in all my various attempts at tweaking the query and other things I had tried but as I went to hit post I had a brainwave and sorted it!! Simply put, I put the wrong call in the html looking for

<?php echo $row_all_games['team_name']; ?>

instead of <?php echo $row_all_games['HomeTeam']; ?>

<?php echo $row_all_games['AwayTeam']; ?>

 

Column 'aliasing' is a new thing to me but I think I get the gist and can understand that

FROM all_games AS AG

basically gives a 'shorthand' version of the 'all_games' table name for future use in the query as does 'HT' and 'VT' for the 'teams' table.

 

I have taken out the AG alias and replaced it with the table names just to stop me getting confused for now. I have also taken out the alias's for home_score and away_score as '*' seems to do the trick with it. I am not picking your code apart as I have not got a clue what I am doing, but it will be easier for me to just restrict it to two alias's for now!!!

 

The query that I used in the end, including the id passed from a previous page is

SELECT *, HT.team_name as HomeTeam, VT.team_name as AwayTeam
FROM all_games, teams 
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams AS VT ON all_games.away_team = VT.team_id  
WHERE '%s' IN (home_team, away_team ) 

 

I will move on in the next couple of days with the next stage of it and a few other columns that need to go in but I know where to come running back to if(when) I hit another problem!

 

Massive thanks once again

Steve