Post reply

SQL Query - problems with Lookup table

  • January 25, 2010 at 6:08 am

    #103886

    Hi

    I am having real problems getting the right solution to a query as follows:

    I have the following tables:

    DB_Results table

    Result_ID Event_Year Driver Co_Driver

    ------------------------------------------

    1 2009 1 2

    2 2008 4 5

    3 2006 4 6

    DB_Drivers table

    Driver_ID Driver_Name

    --------- -------------------

    1 Paul Bourne

    2 Stuart Maloney

    3 Bernard Ten Brinke

    4 Eamonn Boland

    5 Damien Morrissey

    6 Francis Regan

    The result I require is as follows:

    Event_Year Driver_Name Driver_Name

    ----------- ------------ ----------------

    2009 Paul Bourne Stuart Maloney

    2008 Eamonn Boland Damien Morrissey

    2006 Eamonn Boland Francis Regan

    I’ve tried the following:

    SELECT dbo.DB_Drivers.Driver_Name, dbo.DB_Results.Event_Year

    FROM dbo.DB_Results INNER JOIN

    dbo.DB_Events ON dbo.DB_Results.Event_ID = dbo.DB_Events.Event_ID INNER JOIN

    dbo.DB_Drivers ON dbo.DB_Results.Co_Driver = dbo.DB_Drivers.Driver_ID AND dbo.DB_Results.Driver = dbo.DB_Drivers.Driver_ID

    This returns no results. If I take off the last line from ‘AND’ it returns results but I still don’t get the Co-Drivers.

    I also tried adding the Drivers table twice when creating the View but that didn’t work either. I can redesign the tables if you think that would be a better solution.

    Please HELP!

    Thanks

    Lorna

  • January 25, 2010 at 6:16 am

    #1108103

    Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2010 at 6:48 am

    #1108119

    Lorna-331036 (1/25/2010)

    I also tried adding the Drivers table twice when creating the View but that didn’t work either. I can redesign the tables if you think that would be a better solution.

    Please HELP!

    Thanks

    Lorna

    What you said above should work, why wont it?

    SELECT EYEAR, DD.DRIVER_NAME, DD2.DRIVER_NAME as CO_DRIVER_NAME

    FROM DB_RESULTS DR

    JOIN DB_Drivers DD

    ON DR.DRIVER_ID = DD.Driver_ID

    JOIN DB_DRIVERS DD2

    ON DR.CODRIVER = DD2.DRIVER_ID

    As Gail suggested post the data in a consumable format please

    ---------------------------------------------------------------------------------

  • January 26, 2010 at 3:10 am

    #1108530

    Thanks - I tried it again with the 2 Drivers tables and it worked 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply