Joining multiple rows into one row

I've been trying to get this to work for almost a week no with no luck
whatsoever. If anyone can lend a helping hand, I'd be very
appreciative.

Here is my situation. I have a sports website that has a schedule
database. There are three tables in question: game, team, and
game_team.

Table: game
--------------------------
game_id PK
date_time
.... other irrelevant game metadata

Table: team
---------------------------
team_id PK
team_name
.... other irrelevant team metadata

Table: game_team
------------------------------
row_id PK
game_id FK
team_id FK
home_away (can be "H" or "A")
.... other irrelevant data about teams involved in a specific game

If Team A plays Team B at Team B's stadium, then the games table will
get one record added with the game "meta data", including the date and
time, notes about the game, etc. Two records will get added into
game_teams: one for each of the two teams playing and Team A's row has
an "A" in the home_away column and Team B's row has a "H" in the
home_away column.

So the table "game" will look like:
1, "2007-06-09 19:30:00", ...

And the table "team" looks like:
50, "Team A", ...
51, "Team B", ...

And the table "game_team" would look like:
1, 1, 50, "A", ...
2, 1, 51, "H", ...

My problem is, when I try the following SQL:

SELECT t1.team_name, t2.team_name FROM teams AS t1, teams AS t2,
games, game_teams AS gt1, game_teams AS gt2 WHERE
(games.game_id=gt1.game_id AND gt1.team_id=t1.team_id) AND
(games.game_id=gt2.game_id AND gt2.team_id=t2.team_id) AND
games.game_id = 1;

I get 2 records:

t1.team_name | t2.team_name
-----------------------------
Team A | Team B
Team B | Team A

Can someone help me write this so I only get one record? I'm assuming
that my WHERE logic is a bit off. I don't want both game_team rows to
be returned, I just one one row returned that has the data from both
rows.

Any help would be greatly appreciated.
Thanks,
Sam

On Jun 8, 8:58 pm, "bre...@gmail.com" <bre...@gmail.com> wrote:
> I've been trying to get this to work for almost a week no with no luck
> whatsoever. If anyone can lend a helping hand, I'd be very
> appreciative.
>
> Here is my situation. I have a sports website that has a schedule
> database. There are three tables in question: game, team, and
> game_team.
>
> Table: game
> --------------------------
> game_id PK
> date_time
> ... other irrelevant game metadata
>
> Table: team
> ---------------------------
> team_id PK
> team_name
> ... other irrelevant team metadata
>
> Table: game_team
> ------------------------------
> row_id PK
> game_id FK
> team_id FK
> home_away (can be "H" or "A")
> ... other irrelevant data about teams involved in a specific game
>
> If Team A plays Team B at Team B's stadium, then the games table will
> get one record added with the game "meta data", including the date and
> time, notes about the game, etc. Two records will get added into
> game_teams: one for each of the two teams playing and Team A's row has
> an "A" in the home_away column and Team B's row has a "H" in the
> home_away column.
>
> So the table "game" will look like:
> 1, "2007-06-09 19:30:00", ...
>
> And the table "team" looks like:
> 50, "Team A", ...
> 51, "Team B", ...
>
> And the table "game_team" would look like:
> 1, 1, 50, "A", ...
> 2, 1, 51, "H", ...
>
> My problem is, when I try the following SQL:
>
> SELECT t1.team_name, t2.team_name FROM teams AS t1, teams AS t2,
> games, game_teams AS gt1, game_teams AS gt2 WHERE
> (games.game_id=gt1.game_id AND gt1.team_id=t1.team_id) AND
> (games.game_id=gt2.game_id AND gt2.team_id=t2.team_id) AND
> games.game_id = 1;
>
> I get 2 records:
>
> t1.team_name | t2.team_name
> -----------------------------
> Team A | Team B
> Team B | Team A
>
> Can someone help me write this so I only get one record? I'm assuming
> that my WHERE logic is a bit off. I don't want both game_team rows to
> be returned, I just one one row returned that has the data from both
> rows.
>
> Any help would be greatly appreciated.
> Thanks,
> Sam

You have a problem with your table structure. In particular, the way
it's setup now a game can have no teams, just one team, or even three
teams. Is that correct?

You should really get rid of the game_team table and change the games
table to look something like this:

Table: game
--------------------------
game_id PK
date_time
home_team_id
away_team_id
.... other irrelevant game metadata

Then the query becomes really simple.

Given your current table setup, though, you can add the following two
conditions to your where clause to just get a single row:

gt1.home_away = 'H'
gt2.home_away = 'A'

>
> You have a problem with your table structure. In particular, the way
> it's setup now a game can have no teams, just one team, or even three
> teams. Is that correct?

Technically, yes. The reason I made it this way is because some teams
have open dates where no opponent is scheduled, but they still appear
in the schedule as having an open date. So, if you queried on team A's
schedule, it may look like:

m/d/y @Team B
m/d/y Team C
m/d/y - OPEN -
....

So, in that instance, there would only be team A listed for that third
game as the home team with no corresponding away team. But I think I
am going to go ahead and restructure it the way you said, but just
allow for a null value in the away_team_id field.

I'm going to play around more with that option and see what I can come
up with. Thanks for your help.

-Sam