Multiple joins from same table?

------=_NextPart_000_000D_01CA7983.EE274C70
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Good Day,



I am attempting to do something new (to me) with MySQL. I am looking to have
my query return with the value in the visitor and home columns replaced with
the corresponding team name from the teams table. schedule.visitor and
schedule.home are essentially foreign keys to teams.team_no (though I have
not defined them as such yet). What I have been trying is the select
statement below which is fine when joining using one team (say the home
team), but as soon as I attempt to add in the visitor team, things fall
apart.



I am basically looking to have the following outcome:



Oct. 30 - Titans vs. Hawks (7:30 PM)

Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM)



I would like this handled by MySQL instead of PHP if possible.



---------------------------------

Schedule table

'id' int,

'date_time' datetime,

'visitor' tinyint

'home' tinyint

---------------------------------

teams table

'id' int

'team_no' smallint,

'team_name' varchar (20)



---------------------------------



SELECT

schedule.date_time,

teams.team_name

FROM schedule, sojhlteams

WHERE

schedule.visitor = teams.team_no

---------------------------------



Any help is appreciated.



Best Regards,

Terry




------=_NextPart_000_000D_01CA7983.EE274C70--

Re: Multiple joins from same table?

Terry Van de Velde wrote:
> Good Day,
>
>
>
> I am attempting to do something new (to me) with MySQL. I am looking to have
> my query return with the value in the visitor and home columns replaced with
> the corresponding team name from the teams table. schedule.visitor and
> schedule.home are essentially foreign keys to teams.team_no (though I have
> not defined them as such yet). What I have been trying is the select
> statement below which is fine when joining using one team (say the home
> team), but as soon as I attempt to add in the visitor team, things fall
> apart.
>
>
select * from table as t1,table as t2,table as t3 where
t1.column1='blah' and t2.column2='blah' and t3.column3='blah'

does the same thing as

select * from table where column1='blah' and column2='blah' and
column3='blah'

Does that do what you're after?

Gary

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Multiple joins from same table?

Terry Van de Velde wrote:
> Good Day,
>
> I am attempting to do something new (to me) with MySQL. I am looking to have
> my query return with the value in the visitor and home columns replaced with
> the corresponding team name from the teams table. schedule.visitor and
> schedule.home are essentially foreign keys to teams.team_no (though I have
> not defined them as such yet). What I have been trying is the select
> statement below which is fine when joining using one team (say the home
> team), but as soon as I attempt to add in the visitor team, things fall
> apart.
>
> I am basically looking to have the following outcome:
> Oct. 30 - Titans vs. Hawks (7:30 PM)
> Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM)
>
> I would like this handled by MySQL instead of PHP if possible.
>
> Schedule table
> 'id' int,
> 'date_time' datetime,
> 'visitor' tinyint
> 'home' tinyint
>
>
> teams table
> 'id' int
> 'team_no' smallint,
> 'team_name' varchar (20)
>
> SELECT
> schedule.date_time,
> teams.team_name
> FROM schedule, sojhlteams
> WHERE
> schedule.visitor = teams.team_no
>
> Any help is appreciated.
>

The trick to using the same table two or more times in the same query is
through something called "aliases" when you alias a column or table
you give it a different name and will make it easier to manage.

Something like this is what you are after

SELECT
s.date_time
, th.team_name home_team
, ta.team_name away_team
FROM schedule s
INNER JOIN teams th
ON th.team_no = s.home
INNER JOIN teams ta
ON ta.team_no = s.visitor

Here you can see that I aliased the `teams` table twice. Once to handle
the "home team" information (th) and once for the away team info (ta). I
also aliased the team_name columns to make them less confusing labeling
one as "home_team" and other as "away_team".

I think that once you get a grip on how to use aliases, all of this
multiple-table stuff will start to become much easier.
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Multiple joins from same table?

Shawn,

Thanks for the info, it does help indeed.

I had also replied back to Gary to thank him as well, but I don't think =
that
it made it to the list... so to Gary, thanks as well.

Regards,
Terry

Terry Van de Velde
Email: byakko@rogers.com
Phone: (519) 685-0295
Cell:=A0 (519) 619-0987


-----Original Message-----
From: Shawn.Green@Sun.COM [mailto:Shawn.Green@Sun.COM]=20
Sent: December 12, 2009 4:39 PM
To: Terry Van de Velde
Cc: mysql@lists.mysql.com
Subject: Re: Multiple joins from same table?

Terry Van de Velde wrote:
> Good Day,
>=20
> I am attempting to do something new (to me) with MySQL. I am looking =
to
have
> my query return with the value in the visitor and home columns =
replaced
with
> the corresponding team name from the teams table. schedule.visitor =
and
> schedule.home are essentially foreign keys to teams.team_no (though I =
have
> not defined them as such yet). What I have been trying is the select
> statement below which is fine when joining using one team (say the =
home
> team), but as soon as I attempt to add in the visitor team, things =
fall
> apart.
>=20
> I am basically looking to have the following outcome:
> Oct. 30 - Titans vs. Hawks (7:30 PM)
> Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM)
>=20
> I would like this handled by MySQL instead of PHP if possible.
>=20
> Schedule table
> 'id' int,
> 'date_time' datetime,
> 'visitor' tinyint
> 'home' tinyint
>=20
>=20
> teams table
> 'id' int
> 'team_no' smallint,
> 'team_name' varchar (20)
>=20
> SELECT
> schedule.date_time,
> teams.team_name
> FROM schedule, sojhlteams
> WHERE
> schedule.visitor =3D teams.team_no
>=20
> Any help is appreciated.
>=20

The trick to using the same table two or more times in the same query is =

through something called "aliases" when you alias a column or table=20
you give it a different name and will make it easier to manage.

Something like this is what you are after

SELECT
s.date_time
, th.team_name home_team
, ta.team_name away_team
FROM schedule s
INNER JOIN teams th
ON th.team_no =3D s.home
INNER JOIN teams ta
ON ta.team_no =3D s.visitor

Here you can see that I aliased the `teams` table twice. Once to handle=20
the "home team" information (th) and once for the away team info (ta). I =

also aliased the team_name columns to make them less confusing labeling=20
one as "home_team" and other as "away_team".

I think that once you get a grip on how to use aliases, all of this=20
multiple-table stuff will start to become much easier.
--=20
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dbyakko@rogers.com

No virus found in this incoming message.
Checked by AVG - www.avg.com=20
Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: =
12/12/09
14:39:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg