Post reply

Possible Join of One Table Field to Two Master Table Fields?

  • November 17, 2008 at 9:32 am

    #196365

    Came across a small problem.

    I have a master table that contains a record of each member. In this talbe there are two fields; "HomeState", and "WorkState". Both of these fields have a Int value representing the ID value of the corresponding state in the States table.

    I'm trying to pull back the data as such:

    Member, HomeState, WorkState

    Joe.Smoe, TX, AZ

    Jane.Doe, VA, DC

    What sort of JOIN would one use to accomplish this? Is it even possible?

    Cheers.

  • November 17, 2008 at 9:36 am

    #899397

    Liam, you need to join the states table twice:

    SELECT m.member, h.HomeState, m.WorkState

    FROM master m

    LEFT JOIN States h ON h.[ID] = m.HomeState

    LEFT JOIN States w ON w.[ID] = m.WorkState

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • November 17, 2008 at 9:46 am

    #899411

    You're a champion, Chris!

    Thank you very much! 😀

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

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