SQL Server - geschachtelte abfrage oder join?

Asked By Martin

10-Sep-09 11:59 AM

hallo allerseits!
wie erstelle ich eine abfrage aus einer tabelle 'games' einer datenbank
'football' mit drei tabellen wie unten, die folgendes anzeigt:

Bremen Hamburg Weserstadium 01.07.2008 15:30 0 0

?
sollte ich die tabellen anders gestalten? oder l????t sich das machen?
danke f??r antwort!
Martin


CREATE TABLE [dbo].[Teams](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NameFull] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[NameAbbr] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [FootballTests].[dbo].[Teams]
([NameFull]
,[NameAbbr])
VALUES	(N'Werder Bremen',N'Bremen')

INSERT INTO [FootballTests].[dbo].[Teams]
([NameFull]
,[NameAbbr])
VALUES (N'Hamburger SV', N'Hamburg')


CREATE TABLE [dbo].[Stadiums](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CityAndName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Stadiums] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [FootballTests].[dbo].[Stadiums]
([CityAndName])
VALUES
(N'Weserstadion')


CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Team1] [int] NOT NULL,
[Team2] [int] NOT NULL,
[Stadium] [int] NOT NULL,
[TimeRegular] [datetime] NOT NULL,
[Result1] [int] NOT NULL,
[Result2] [int] NOT NULL,
CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Games]  WITH CHECK ADD  CONSTRAINT [FK_Games_Stadium]
FOREIGN KEY([Stadium])
REFERENCES [dbo].[Stadiums] ([ID])
GO
ALTER TABLE [dbo].[Games]  WITH CHECK ADD  CONSTRAINT [FK_Games_Team1]
FOREIGN KEY([Team1])
REFERENCES [dbo].[Teams] ([ID])
GO
ALTER TABLE [dbo].[Games]  WITH CHECK ADD  CONSTRAINT [FK_Games_Team2]
FOREIGN KEY([Team2])
REFERENCES [dbo].[Teams] ([ID])

INSERT INTO [FootballTests].[dbo].[Games]
([Team1]
,[Team2]
,[Stadium]
,[TimeRegular]
,[Result1]
,[Result2])
VALUES
(1, 2, 1, '1.7.2008 15:30', 0, 0)

11-Sep-09 02:44 AM

Hallo Martin,

ich w??rde es so machen:

select
t1.NameAbbr
,t2.NameAbbr
,s.CityAndName
,g.TimeRegular
,g.Result1
,g.Result2
from dbo.Games g
inner join dbo.Teams t1
on
g.Team1 = t1.ID
inner join dbo.Teams t2
on
g.Team2 = t2.ID
inner join dbo.Stadiums s
on
s.ID = g.Stadium


Viele Gr????e
Christoph

11-Sep-09 03:11 AM

Hallo Christoph,
danke Dir vielmals!
ich wu??te u.a. nicht, da?? ich variablen f??r tabellen anf??hren kann, bevor
sie definiert wurden...
einen sch??nen tag!
Martin