CODEWITHSUNDEEP
phpmysqlsql
Chris Needham
Chris Needham

Reputation: 75

Alias Column After SQL Query

Evening, wasn't sure how to title my question but I have an issue that's been driving me mad and I can't seem to find an answer so I'm hoping you kind folks can point me in the right direction.

I'm currently making a sports predictions website on the side to learn more about PHP and SQL. So I have the following databases...

Teams
 id  |  team_name  |  active
  1       team 1        1
  2       team 2        0
  3       team 3        1

Matches
 id  |  hometeam  |  homescore  |  awayteam  |  awayscore
  1        3             1            4            0
  2        5             2            1            3
  3        1             0            3            2

To put it simply anyway. What I want is to lookup the team ids in hometeam and awayteam against the team name in the Teams table. So I've got this so far:

SELECT * FROM Matches LEFT JOIN Teams AS home ON Matches.hometeam = home.id LEFT JOIN Teams AS away ON Matches.awayteam = away.id

Which actually does do it except it leaves me with 2 columns called "team_name" one with the home team and one with the away team.

So my question is this, can I alias/rename the columns to 'home' and 'away' or is there a way that on my page I can distinguish between the two? For example I have $row['team_name'] but how do I point it to the right team_name column? If that makes sense.

Any help at all in pointing me in the right direction would be very much appreciated, thank you.

Upvotes: 1

Views: 37

Answers (2)

Kateract
Kateract

Reputation: 852

Specify the column names instead of using SELECT *

SELECT home.team_name AS home_team, away.team_name AS away_team, homescore, awayscore
FROM Matches
LEFT JOIN Teams AS home ON Matches.hometeam = home.id 
LEFT JOIN Teams AS away ON Matches.awayteam = away.id

You should not use SELECT * in your queries, for many reasons explained thoroughly elsewhere.

Upvotes: 1

Tudor Constantin
Tudor Constantin

Reputation: 26861

you can do it like this:

SELECT home.team_name as home_team, away.team_name as away_team FROM Matches LEFT JOIN Teams AS home ON Matches.hometeam = home.id LEFT JOIN Teams AS away ON Matches.awayteam = away.id

Upvotes: 1

Related Questions