Alias Column After SQL Query

Question

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.

You should not use `SELECT *`, but specify the column names. Then you can alias them just like you aliased your tables. — Kateract, Apr 19 '16 at 17:53

score 1 · Accepted Answer · answered Apr 19 '16 at 17:55

1

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

answered Apr 19 '16 at 17:55

Tudor Constantin

26,330
7
49
72

1

Worked like a charm, I knew there was a simple explanation. Thanks very much for your help. – Chris Needham Apr 19 '16 at 18:58

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

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.

edited May 23 '17 at 11:52

Community

1
1

answered Apr 19 '16 at 17:58

Kateract

822
6
15

Thanks for your answer. I've seen SELECT * is frowned upon so will be tidying it up once it all works, much appreciated. – Chris Needham Apr 19 '16 at 19:00

Alias Column After SQL Query

2 Answers2