Solved

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

Posted on 2004-10-15
4
244 Views
Last Modified: 2012-05-05
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
Comment
Question by:bradtm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
boblah earned 500 total points
ID: 12322439
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12322464
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12322516
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
 

Author Comment

by:bradtm
ID: 12322594
ah yes. it's Friday...I was spacing out on the aliasing. thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question