sql join query

[dylan]

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

[Paul Lautman]

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]

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

[strawberry]

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

?

[dylan]

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

[strawberry]

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.

[paul_lautman@yahoo.com]

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.

[dylan]

Hi,

Sorry, my mistake, but keep your hair on!

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

[Captain Paralytic]

dylan wrote:

> Hi,
>
> Sorry, my mistake, but keep your hair on!

Too late for that, lost mine years ago!