• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Selecting attributes from two different tables which both refrence to same lookup table

In this case I'm trying to get two different County names in the select clause from two different tables, but the attributes in the table, both CountyID, refer to the uCounty table. Ultimately I'm running into selecting uCounty.description for both.  I'm not sure if there's an alias somewhere that could help me avoid this.
Here's what I have so far as a test. The actual statement i'm using is much more complex, but proceeding query, which is very wrong, should give you an idea.

SELECT     dbo.uCounty.Description, dbo.uCounty.Description AS Expr1
FROM         dbo.tApplicant INNER JOIN
                      dbo.tWorkHistory ON dbo.tApplicant.ApplicantID = dbo.tWorkHistory.ApplicantId LEFT OUTER JOIN
                      dbo.uCounty ON dbo.uCounty.CountyId = dbo.tWorkHistory.CountyID LEFT OUTER JOIN
                      dbo.uCounty uCounty_1 ON dbo.uCounty.CountyId = dbo.tApplicant.CountyID


uCounty
-------
CountyID
Description

tApplicant
------------
ApplicantID
...
CountyID

tWorkHistory
--------------
WorkHistoryID
ApplicantID
...
CountyID
0
bradtm
Asked:
bradtm
  • 2
1 Solution
 
boblahCommented:
Hi bradtm,

You want

SELECT     dbo.uCounty.Description, dbo.uCounty.Description AS Expr1
FROM         tApplicant a
                    INNER JOIN tWorkHistory wh
                        ON a.ApplicantID = wh.ApplicantId
                    LEFT OUTER JOIN dbo.uCounty cwh
                        ON cwh.CountyId = wh.CountyID
                    LEFT OUTER JOIN dbo.uCounty ca
                        ON ca.CountyId = a.CountyID



Cheers!
0
 
jdlambert1Commented:
SELECT     dbo.uCounty.Description, dbo.uCounty_1.Description AS Expr1
FROM        dbo.tApplicant
 INNER JOIN dbo.tWorkHistory ON dbo.tApplicant.ApplicantID = dbo.tWorkHistory.ApplicantId
 LEFT JOIN dbo.uCounty ON dbo.uCounty.CountyId = dbo.tWorkHistory.CountyID
 LEFT JOIN dbo.uCounty uCounty_1 ON dbo.uCounty.CountyId = dbo.tApplicant.CountyID
0
 
jdlambert1Commented:
Your join conditions are critical to your results, but when you join to uCounty twice, and want it's description column from each virtual copy, you need to modify your SELECT clause to use the alias:

dbo.uCounty.Description, dbo.uCounty_1.Description AS Expr1
0
 
bradtmAuthor Commented:
ah yes. it's Friday...I was spacing out on the aliasing. thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now