I have 2 columns with data in different sequences in one table referencing a single column in different table.

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!


I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <-------
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)


TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.




CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)


Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form


CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteID

I am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:

SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID

In your query, this bit looks to me like a syntax error:

(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers

... unless SQL Server has a very different SQL syntax that the one I know.

Sorry about the confusion but thats it. Thank you