resolve id's to variable field names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

resolve id's to variable field names

Author

Topic

conslow
Starting Member

2 Posts

Posted - 2006-07-10 : 17:14:54

I have 2 tables. 1 has a project name, a "manager" field and a "technician" field.
The other table is an employee data table.

The first table lists the "manager" field as the employees initials, as does the "technician" field.

I need to select the project name from the first table and resolve the manager initials and technician initials to the first and last name fields in the employee table....
e.g.
table 1

Project Name___Manager___Technician
MyProject_____RGA________BRJ

table 2
employeecode_firstname__lastname
rja___________Roger____Anderson
brj___________Bob_____ Jones

I need to return the following:

Project Name___Manager___________Technician
My Project_____Roger_Anderson____Bob_ Jones

What is the most efficient way to do this?
I have looked into joins, unions, temp tables but cannot seem to get it to work. It seems to have issues determining the manager firstname and lastname from the technician first and last name.

Any suggestions, links, etc would be much appreciated.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-11 : 03:14:07

[code]
declare @Project table(projectname varchar(100),MgrCode varchar(3),TechCode varchar(3))
declare @employee table(empcode varchar(3),firstname varchar(50),lastname varchar(50))

insert into @project(projectname,mgrcode,techcode)
select 'myproject','rga','brj'

insert into @employee(empcode,firstname,lastname)
select 'rga','Roger','Anderson'
union select 'brj','Bob','Jones'

select p.projectname as 'Project Name',(select firstname + ' ' + lastname from @employee where empcode=p.mgrcode) as 'Manager',
(select firstname + ' ' + lastname from @employee where empcode=p.techcode) as 'Technician'
from @project p
[/code]

--------------------
keeping it simple...

conslow
Starting Member

2 Posts

Posted - 2006-07-11 : 17:38:13

Jen,
Excellent...thank you!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-11 : 18:39:47

[code]select p.[Project Name], m.firstname + ' ' + m.lastname as [Manager], t.firstname + ' ' + t.lastname as [Technician]
from project p inner join employee m
on p.manager = m.employeecode
inner join employee t
on p.technician = t.employeecode[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources