Professional Web Applications Themes

sql join query - MySQL

Hi, I have a little problem that's driving me nuts, I'm sure there's a simple solution that I'm overlooking. The problem is this (I'm giving a simplified example): I have two tables: matches (match_id, team1_id, team2_id) teams (team_id, team_name) I need a query that shows: team_id, team1, team2 (where team1 and team 2 are looked up from the teams table) I hope someone can help! Thanks...

  1. #1

    Default sql join query

    Hi,

    I have a little problem that's driving me nuts, I'm sure there's a
    simple solution that I'm overlooking.


    The problem is this (I'm giving a simplified example):

    I have two tables:

    matches (match_id, team1_id, team2_id)
    teams (team_id, team_name)

    I need a query that shows:

    team_id, team1, team2 (where team1 and team 2 are looked up from the
    teams table)


    I hope someone can help!

    Thanks

    dylan Guest

  2. #2

    Default Re: sql join query

    dylan wrote:
    > Hi,
    >
    > I have a little problem that's driving me nuts, I'm sure there's a
    > simple solution that I'm overlooking.
    >
    >
    > The problem is this (I'm giving a simplified example):
    >
    > I have two tables:
    >
    > matches (match_id, team1_id, team2_id)
    > teams (team_id, team_name)
    >
    > I need a query that shows:
    >
    > team_id, team1, team2 (where team1 and team 2 are looked up from the
    > teams table)
    >
    >
    > I hope someone can help!
    >
    > Thanks
    What you are saying doesn't make sense?

    I assume that when you say you want "team_id, team1, team2" you mean team1 =
    team1_name and team2 = team2_name?

    But if that is the case, which team_id are you askinf for in the results,
    that of team1 or that of team2?

    If so this is a basic join as shown in the manual
    SELECT


    Paul Lautman Guest

  3. #3

    Default Re: sql join query

    team1 and team2 will have a different team_id in the matches table.


    so basically, i want a query that says something like

    Team_id team1_name team2_name
    0 Team A Team B
    1 Team C Team A
    2 Team A Team C


    where in the matches table the data is stored:


    match_id team1_id team2_id
    0 0 1
    1 2 0
    2 0 2


    and in the teams table


    team_id team_name
    0 team A
    1 team B
    2 team C




    Does that make any more sense?







    Paul Lautman wrote:
    > dylan wrote:
    > > Hi,
    > >
    > > I have a little problem that's driving me nuts, I'm sure there's a
    > > simple solution that I'm overlooking.
    > >
    > >
    > > The problem is this (I'm giving a simplified example):
    > >
    > > I have two tables:
    > >
    > > matches (match_id, team1_id, team2_id)
    > > teams (team_id, team_name)
    > >
    > > I need a query that shows:
    > >
    > > team_id, team1, team2 (where team1 and team 2 are looked up from the
    > > teams table)
    > >
    > >
    > > I hope someone can help!
    > >
    > > Thanks
    > What you are saying doesn't make sense?
    >
    > I assume that when you say you want "team_id, team1, team2" you mean team1 =
    > team1_name and team2 = team2_name?
    >
    > But if that is the case, which team_id are you askinf for in the results,
    > that of team1 or that of team2?
    >
    > If so this is a basic join as shown in the manual
    > SELECT
    dylan Guest

  4. #4

    Default Re: sql join query


    dylan wrote:
    > team1 and team2 will have a different team_id in the matches table.
    >
    >
    > so basically, i want a query that says something like
    >
    > Team_id team1_name team2_name
    > 0 Team A Team B
    > 1 Team C Team A
    > 2 Team A Team C
    >
    >
    > where in the matches table the data is stored:
    >
    >
    > match_id team1_id team2_id
    > 0 0 1
    > 1 2 0
    > 2 0 2
    >
    >
    > and in the teams table
    >
    >
    > team_id team_name
    > 0 team A
    > 1 team B
    > 2 team C
    >
    >
    >
    >
    > Does that make any more sense?
    >
    >
    >
    >
    >
    >
    >
    > Paul Lautman wrote:
    >
    > > dylan wrote:
    > > > Hi,
    > > >
    > > > I have a little problem that's driving me nuts, I'm sure there's a
    > > > simple solution that I'm overlooking.
    > > >
    > > >
    > > > The problem is this (I'm giving a simplified example):
    > > >
    > > > I have two tables:
    > > >
    > > > matches (match_id, team1_id, team2_id)
    > > > teams (team_id, team_name)
    > > >
    > > > I need a query that shows:
    > > >
    > > > team_id, team1, team2 (where team1 and team 2 are looked up from the
    > > > teams table)
    > > >
    > > >
    > > > I hope someone can help!
    > > >
    > > > Thanks
    > > What you are saying doesn't make sense?
    > >
    > > I assume that when you say you want "team_id, team1, team2" you mean team1 =
    > > team1_name and team2 = team2_name?
    > >
    > > But if that is the case, which team_id are you askinf for in the results,
    > > that of team1 or that of team2?
    > >
    > > If so this is a basic join as shown in the manual
    > > SELECT
    I don't understand it either. Did you mean:
    > match_id team1_name team2_name
    > 0 Team A Team B
    > 1 Team C Team A
    > 2 Team A Team C
    ?

    strawberry Guest

  5. #5

    Default Re: sql join query

    yeah, that's the result I want. Any ideas?

    dylan Guest

  6. #6

    Default Re: sql join query


    dylan wrote:
    > yeah, that's the result I want. Any ideas?
    Well in that case it's easy:

    SELECT m1.match_id, t1.team_name AS team1_name,t2.team_name AS
    team2_name
    FROM matches AS m1
    LEFT JOIN teams AS t1 ON m1.team1_id = t1.team_id
    LEFT JOIN teams AS t2 ON m1.team2_id = t2.team_id

    I added the 'AS's for clarity - but it'll work without them.

    strawberry Guest

  7. #7

    Default Re: sql join query


    dylan wrote:
    > yeah, that's the result I want. Any ideas?
    When it comes down to it, computer programming is just a matter of
    putting down a few words.

    However it is IMPORTANT to put the CORRECT words in the CORRECT order.

    What confused us all was that you repeatedly said that you wanted
    team_id in the result set and yet you said that the team ids for both
    teams were different. Even after I asked which of the 2 team ids you
    wanted in the result set, you still replied that you wanted Team_id in
    it.

    If you'd put match_id in the required result set, you'd have got an
    answer immediately.

    paul_lautman@yahoo.com Guest

  8. #8

    Default Re: sql join query

    Hi,

    Sorry, my mistake, but keep your hair on!

    Anyway, thanks strawberry you've saved me a lot of stress.

    dylan Guest

  9. #9

    Default Re: sql join query


    dylan wrote:
    > Hi,
    >
    > Sorry, my mistake, but keep your hair on!
    Too late for that, lost mine years ago!

    Captain Paralytic Guest

Similar Threads

  1. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  2. Add another join to a query
    By Conti in forum Coldfusion Database Access
    Replies: 6
    Last Post: March 20th, 06:23 PM
  3. INNER JOIN query question
    By Bosconian in forum MySQL
    Replies: 6
    Last Post: February 27th, 11:21 PM
  4. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 PM
  5. Query, Join on nearest
    By Tommy in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 10th, 08:26 PM

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