Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Question Unanswered: Help with Select statement

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with Select statement

    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.

  3. #3
    Join Date
    Feb 2003
    Posts
    5

    Re: Help with Select statement

    Sorry about the confusion but thats it. Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •