Selecting attributes from two different tables which both refrence to same lookup table
Posted on 2004-10-15
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