Multiple joins in a query

#1

I have a database I use to keep track of team scores in a league.

My teams table is something like this
TeamID = PK
Name

My games table is something like this
GameID = PK
AwayID
Away Score
HomeID
Home Score

AwayID and HomeID are both integers that correspond to a TeamID.

I would like a query that returned the two teams by name as well as their
scores. I have been able to get one name but not both.

TIA
Mike

#2

On 9/17/2011 6:00 PM, Michael Farnham wrote:

Quote:

I have a database I use to keep track of team scores in a league.

My teams table is something like this
TeamID = PK
Name

My games table is something like this
GameID = PK
AwayID
Away Score
HomeID
Home Score

AwayID and HomeID are both integers that correspond to a TeamID.

I would like a query that returned the two teams by name as well as their
scores. I have been able to get one name but not both.

TIA
Mike

You simply need to start with the games table and perform two joins on
the teams table - one for away team and one for home team.

Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore
FROM games g
JOIN teams a ON g.AwayID = a.TeamID
JOIN teams h ON g.HomeID = h.TeamID

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#3

On Sat, 17 Sep 2011 20:09:15 -0400, Jerry Stuckle wrote:

Quote:

On 9/17/2011 6:00 PM, Michael Farnham wrote:
I have a database I use to keep track of team scores in a league.

My teams table is something like this TeamID = PK
Name

My games table is something like this GameID = PK
AwayID
Away Score
HomeID
Home Score

AwayID and HomeID are both integers that correspond to a TeamID.

I would like a query that returned the two teams by name as well as
their scores. I have been able to get one name but not both.

TIA
Mike

You simply need to start with the games table and perform two joins on
the teams table - one for away team and one for home team.

Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games g
JOIN teams a ON g.AwayID = a.TeamID
JOIN teams h ON g.HomeID = h.TeamID

Thanks a lot Jerry that works perfectly. I am not sure why. Is 'a'
established as an alias for the join between g.AwayID and Teams.TeamID?

#4

On Sat, 17 Sep 2011 20:07:13 -0500, Michael Farnham wrote:

Quote:

On Sat, 17 Sep 2011 20:09:15 -0400, Jerry Stuckle wrote:
You simply need to start with the games table and perform two joins on
the teams table - one for away team and one for home team.

Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games g
JOIN teams a ON g.AwayID = a.TeamID
.. ^^^
JOIN teams h ON g.HomeID = h.TeamID

Thanks a lot Jerry that works perfectly. I am not sure why. Is 'a'
established as an alias for the join between g.AwayID and Teams.TeamID?

Yes, but... It's established AT the join, but carries on throughout the
entire query.

--
Kyle J Cardoza <admin (AT) zetachannel (DOT) com> sigged:

Quote:

Faith does not, in fact, move mountains;
Mainly because they won't let her loose with a drilling crew and enough

dynamite. -- Chris Suslowicz in the Monastery

#5

On Sat, 17 Sep 2011 21:05:58 -0500, Peter H. Coffin wrote:

Quote:

On Sat, 17 Sep 2011 20:07:13 -0500, Michael Farnham wrote:
On Sat, 17 Sep 2011 20:09:15 -0400, Jerry Stuckle wrote:
Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games g JOIN teams a
ON g.AwayID = a.TeamID
. ^^^
JOIN teams h ON g.HomeID = h.TeamID

Thanks a lot Jerry that works perfectly. I am not sure why. Is 'a'
established as an alias for the join between g.AwayID and Teams.TeamID?

Yes, but... It's established AT the join, but carries on throughout the
entire query.

Yes I thought that must be what happened. Nevertheless it looks odd to
use an alias before it has been defined. Plus it is not at all obvious
from the syntax that an alias is being created. In any case I believe I
understand what is happening and will be able to use it in the future.
Many thanks for all the help.

Regards
Mike

#6

Michael Farnham wrote:

Quote:

On Sat, 17 Sep 2011 21:05:58 -0500, Peter H. Coffin wrote:

On Sat, 17 Sep 2011 20:07:13 -0500, Michael Farnham wrote:
On Sat, 17 Sep 2011 20:09:15 -0400, Jerry Stuckle wrote:
Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games g JOIN teams a
ON g.AwayID = a.TeamID
. ^^^
JOIN teams h ON g.HomeID = h.TeamID

Thanks a lot Jerry that works perfectly. I am not sure why. Is 'a'
established as an alias for the join between g.AwayID and Teams.TeamID?

Yes, but... It's established AT the join, but carries on throughout the
entire query.

Yes I thought that must be what happened. Nevertheless it looks odd to
use an alias before it has been defined. Plus it is not at all obvious
from the syntax that an alias is being created. In any case I believe I
understand what is happening and will be able to use it in the future.
Many thanks for all the help.

You could have written it like this:

SELECT A.Name, AwayScore, H.Name, HomeScore
FROM games AS g
JOIN teams AS a ON g.AwayID = a.TeamID
JOIN teams AS h ON g.HomeID = h.TeamID

Most people just save 3 key presses by skipping the "AS" part, not sure if
dropping the "AS" is ISO compliant.

--

//Aho

#7

On Sun, 18 Sep 2011 09:09:51 +0200, J.O. Aho wrote:

Quote:

You could have written it like this:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games AS g
JOIN teams AS a ON g.AwayID = a.TeamID JOIN teams AS h ON g.HomeID =
h.TeamID

Most people just save 3 key presses by skipping the "AS" part, not sure
if dropping the "AS" is ISO compliant.

That is much clearer to me. I guess when one gets used to it they are
both clear, but with my level of inexperience it was not obvious what
the method without the 'AS' was doing.

Regards
Mike

#8

On 2011-09-18 09:09, J.O. Aho wrote:
[...]

Quote:

Most people just save 3 key presses by skipping the "AS" part, not sure if
dropping the "AS" is ISO compliant.

It is ok according to standard, but I would say that the "AS", makes the
query easier to understand. Skipping as in combination with using
reserved words as identifiers can make it utterly confusing. In an old
thread for another dbms a query like:

SELECT c1 FROM T1 OUTER UNION SELECT c1 FROM T2

where discussed. Clearly

SELECT c1 FROM T1 AS OUTER UNION SELECT c1 FROM T2

would have caused less confusion.

/Lennart

#9

On 9/18/2011 5:51 AM, Lennart Jonsson wrote:

Quote:

On 2011-09-18 09:09, J.O. Aho wrote:
[...]
Most people just save 3 key presses by skipping the "AS" part, not sure if
dropping the "AS" is ISO compliant.

It is ok according to standard, but I would say that the "AS", makes the
query easier to understand. Skipping as in combination with using
reserved words as identifiers can make it utterly confusing. In an old
thread for another dbms a query like:

SELECT c1 FROM T1 OUTER UNION SELECT c1 FROM T2

where discussed. Clearly

SELECT c1 FROM T1 AS OUTER UNION SELECT c1 FROM T2

would have caused less confusion.

/Lennart

ANSI/ISO standards do not allow keywords to be used as aliases.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#10

On Sat, 17 Sep 2011 23:44:51 -0500, Michael Farnham wrote:

Quote:

On Sat, 17 Sep 2011 21:05:58 -0500, Peter H. Coffin wrote:

On Sat, 17 Sep 2011 20:07:13 -0500, Michael Farnham wrote:
On Sat, 17 Sep 2011 20:09:15 -0400, Jerry Stuckle wrote:
Something like:

SELECT A.Name, AwayScore, H.Name, HomeScore FROM games g JOIN teams a
ON g.AwayID = a.TeamID
. ^^^
JOIN teams h ON g.HomeID = h.TeamID

Thanks a lot Jerry that works perfectly. I am not sure why. Is 'a'
established as an alias for the join between g.AwayID and Teams.TeamID?

Yes, but... It's established AT the join, but carries on throughout the
entire query.

Yes I thought that must be what happened. Nevertheless it looks odd to
use an alias before it has been defined.

SQL is *supposed to be* atomic. Or originally was, or the goal is to be,
or however you want to look at something being "mostly but not
necessarily always is". There's even special syntax to specify "when" to
actually pick rows so that you can clarify whether you need to use
derived result: the difference between WHERE criteria and HAVING
criteria. But that distinction goes to serve that atomicity and not
having to know exactly how the engine is doing things, only what results
you want.

--
18. I will not have a son. Although his laughably under-planned attempt
to usurp power would easily fail, it would provide a fatal
distraction at a crucial point in time.
--Peter Anspach's list of things to do as an Evil Overlord