Perplexing Join

Hi,

I'm having a hard time figuring this out. Let me start by explaining my setup. I have a database that will hold high school football statistics. The two tables to focus on are the games table and the schools table--schema below:

schools
----
school_id (varchar, 4, unique)
school_name (varchar, 32)
school_city (varchar, 32)
school_district (varchar, 32)
games
----
game_id (int, identity, unique)
game_datetime (smalldatetime)
game_stadium (varchar, 32)
team_home (varchar, 4)
team_away (varchar, 4)

I didn't bother creating a separate stadiums table, because games will take place in only two stadiums. But here's what the row(s) I want to look like:

game_datetime, game_stadium, school_name (for team_home), school_name (for team_away)

The trouble is with pulling the school names for both teams from the schools table. I tried this query:

SELECT *FROM gamesINNERJOIN schoolsON team_home = school_idOR team_away = school_id

That pulls two rows for each game (one with the home_team's info and another with the away team's info).

The way the tables are constructed makes sense to me, but I'm not opposed to changing the schema. Perhaps you can point me in a different direction.

Thanks

Try this and see if it gives you the results your looking for

Select g.game_datetime, g.game_stadium, h.school_name, a.school_name

FROM Games g

Inner Join schools h on g.team_home = h.school_id

Inner Join schools a on g.team_away = a.school_id

|||

Oh wow! I didn't know you could do that. That's awesome!

Thanks!

PS: Is this a perfectly normal way to do this, or should I reconsider changing my tables?

|||

your tables are fine. Only thing I would do in changing your tables is to maybe change the names of the team_home | team_away to team_home_id | team_away_id. It has no affect at all on the outcome of your data. But 6 months from now you will know that those two fields are FK relations to school_id in your school table. Which helps from a management point of view.

And yes doing joins like this is perfectly fine.

|||

Thanks! You have no idea how much easier my life just got