Join Tables. - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join Tables.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-15 : 10:28:35

Games Table:
CREATE games (id, title, description, type)
INSERT INTO games (title, description, type) VALUES (Dodgeball, a game, 1)
INSERT INTO games (title, description, type) VALUES (Bopper, a game, 4)
CREATE gametypes (id, type)
INSERT INTO gametype (type) VALUES (active inside)
INSERT INTO gametype (type) VALUES (chair game)
==
So, I have two tables. There are actually ten types of games, but two samples should do. I want to perform a join (I think, unless there is an easier way) that joins the tables where the games.type=gametype.id but have it display not the gametype.id but the gametype.type.
So I tried something like:
SELECT * FROM games G LEFT JOIN gametypes GT ON G.Type=GT.ID
But the result set as may be excepted gives back the following:
Dodgeball, a game, 1
Whereas what I want is:
Dodgeball, a game, active inside
Any ideas? Thanks.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-15 : 10:44:33

Think about what you are asking for a little more

SELECT * FROM games G LEFT JOIN gametypes GT ON G.Type=GT.ID

is not going to get what you are asking for so..

SELECT g.title, g.description, gt.type FROM games G LEFT JOIN gametypes GT ON G.Type=GT.ID

-------
Moo. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-15 : 10:46:37

SELECT G.title,G.description, GT.type FROM games G LEFT JOIN gametypes GT ON G.Type=GT.ID

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-15 : 10:56:14

Not sure if this is what you want....but it was a good shot at a nice post...

Try it like this though...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE games ([id] int, title varchar(50), [description] varchar(255), type int)
CREATE TABLE gametypes (type int, typeDesc varchar(50))
GO

INSERT INTO games (title, [description], type)
SELECT 'Dodgeball', 'a game', 1 UNION ALL
SELECT 'Bopper', 'a game', 4

INSERT INTO gametypes (type, typedesc) 
SELECT 1, 'active inside' UNION ALL
SELECT 4, 'chair game'
GO

SELECT * FROM games g JOIN gametypes t ON g.type = t.type
GO

SET NOCOUNT OFF
DROP TABLE games
DROP TABLE gameTypes
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources