Beginner--Create SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Beginner--Create SQL Query

Author

Topic

larrythek
Starting Member

7 Posts

Posted - 2008-01-19 : 21:38:14

I'm an Absolute beginner to SQL

I have a table of baseballTeams with columns baseballTeamName and a baseballTeamID

A have another table (baseballGames) which consists of baseball games which includes a homeTeamID and a visitingTeamID.
Both of these columns are linked to the baseballTeamID from the baseballTeams table

I have been successful at joining the two tables and Selecting the baseballTeamName from the baseballTeams table for either the hometeam or
visiting team as shown below:

SELECT gameID, homeTeamID, baseballTeamID,baseballTeamName
FROM baseballGames, baseballTeams
WHERE baseballGames.homeTeamID=baseballTeams.baseballTeamID

...but I cannot figure out how to select BOTH the homeTeam name and the visitingTeam name from the baseballTeam table

Any help would be greatly appreciated

Thanks in advance

Larry

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 22:31:11

just need to join to your teams table twice


SELECT	g.gameID, g.homeTeamId, g.visitingTeamId, home.baseballTeamName, vis.baseballTeamName
FROM	baseballGames g
JOIN	baseballTeams home
On	g.homeTeamID = home.baseballTeamID
JOIN	baseballTeams vis
On	g.visitingTeamId = vis.baseballTeamID

larrythek
Starting Member

7 Posts

Posted - 2008-01-20 : 09:53:10

Thanks Russell...for your response...it worked first shot

...but...

I can't seem to find in any of my references the syntax you use

It appears you have created variables g, home and vis

What is this syntax called....where is it described???

Thanks again

Larry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-20 : 09:59:20

quote:
Originally posted by larrythek

Thanks Russell...for your response...it worked first shot

...but...

I can't seem to find in any of my references the syntax you use

It appears you have created variables g, home and vis

What is this syntax called....where is it described???

Thanks again

Larry

They are not variables but alises for tables. Its like putting another temporary name for tables so that it can distinguish which columns are from which table when selecting/taking join...

larrythek
Starting Member

7 Posts

Posted - 2008-01-20 : 10:11:54

Thanks Visakh for your incredibly speedy response.

Neither of the two texts I'm using describe using aliases as used in Russell's response.

Do you know of any online reference or text book which describes this syntax?

Greetings from New Jersey, USA

Thanks again

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-20 : 12:09:29

see Using Table Alias in Books Online http://msdn2.microsoft.com/en-us/library/ms187455.aspx

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-20 : 12:11:44

You can use the following query also...............

SELECT gameID,
homeTeamId,
visitingTeamId,
( SELECT baseballTeamName
FROM baseballTeams
WHERE baseballTeamID = homeTeamID),
( SELECT baseballTeamName
FROM baseballTeams
WHERE baseballTeamID = visitingTeamId)
FROM baseballGames

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources