Tuesday, March 20, 2012

Alaising the same table multiple times

To All--

First off, I'm new to SQL Server and apologize if this is a trivial question.

What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.

Table1
Emp_Number
Emp_Code_1
Emp_Code_2

Table2 (Contains a list of codes and their related descriptions)
Emp_Code
Long_Desc
Short_Desc

I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.

Hope this makes sense.

CSelect t1.Emp_Number, t1.Emp_Code_1, t2a.Short_Desc, t1.Emp_Code_2, t2b.Short_Desc
FROM Table1 t1
INNER JOIN Table2 t2a on t2a.Emp_Code = t1.Emp_Code_1
INNER JOIN Table2 t2b on t2b.Emp_Code = t1.Emp_Code_2


Same table, different alias.

No comments:

Post a Comment