Professional Web Applications Themes

Joining multiple rows into one row - MySQL

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 ...

  1. #1

    Default 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

    bressi@gmail.com Guest

  2. #2

    Default Re: Joining multiple rows into one row

    On Jun 8, 8:58 pm, "com" <com> wrote: 

    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'

    ZeldorBlat Guest

  3. #3

    Default Re: Joining multiple rows into one row



    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

    bressi@gmail.com Guest

Similar Threads

  1. Joining Multiple Tables
    By kljkhkjhkjh in forum Coldfusion Database Access
    Replies: 7
    Last Post: March 29th, 10:41 PM
  2. Joining Multiple Domains
    By Alan in forum Windows Server
    Replies: 5
    Last Post: July 1st, 01:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139