-6

OK, now that I have your attention, I've got a basic mysql SELECT question that I can't figure out.

I want to return the team names for the code equivalences in the games table. I'm trying to do a lookup of the team name for both the home and away teams. I can get it to result the home or the away team name however can't figure out how to get both?

Ex.

table teams
+----+----+
| code | name |
+----+----+
| 1 | Colts |
+----+----+
| 2 | Falcons |
+----+----+
| 3 | Rams |
+----+----+
| 4 | Jets |
+----+----+

table games
+----+----+
| home | away |
+----+----+
| 1 | 2 |
+----+----+
| 3 | 4 |
+----+----+

Thanks for your help
Kevin

Votes + Comments
Not worth the effort
3
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by apegram
0
Select h.Name as HomeTeam, a.Name as AwayTeam
From teams h
Inner Join games g
on h.code = g.home
Inner Join teams a
on g.away = a.code
0

This is very helpful, thank you.

When I apply this to my larger set of games however, for some reason I get duplicates and more for most of the resulting rows, only some of the resulting rows are unique. I am using a WHERE clause to equate the home and away teams to the name.

Any ideas why I am not getting unique results?

0

This is very helpful, thank you.

When I apply this to my larger set of games however, for some reason I get duplicates and more for most of the resulting rows, only some of the resulting rows are unique. I am using a WHERE clause to equate the home and away teams to the name.

Any ideas why I am not getting unique results?

Duplicates would be indicative of duplicated data in one or more of your source tables or an invalid join in your query.

When you say are using a where clause to equate teams to the name, what do you mean? The SQL statement I provided handled that in the join statements. What are you using as a SQL statement?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.