Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Possible Join of One Table Field to Two Master Table Fields? Expand / Collapse
Author
Message
Posted Monday, November 17, 2008 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 16, 2009 8:07 AM
Points: 11, Visits: 19
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.
Post #603750
Posted Monday, November 17, 2008 9:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:14 AM
Points: 7,176, Visits: 13,626
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
Exploring Recursive CTEs by Example Dwain Camps
Post #603755
Posted Monday, November 17, 2008 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 16, 2009 8:07 AM
Points: 11, Visits: 19
You're a champion, Chris!

Thank you very much! :D
Post #603771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse