Displaying Columns Twice - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Displaying Columns Twice

Author

Topic

castroc
Starting Member

2 Posts

Posted - 2008-05-09 : 21:00:21

Hello All,

I have a little problem. Here is the scenario:

I got two tables Asset and Personnel

Personnel has FirstName, LastName,and username which is unique.

Asset table stores all information related to an asset and the username to whom the asset is assigned and the POC username.

The tables look like this:

Personnel Table
FirstName, Last Name, UserName
John, Doe, jdoe
Bill, Smith, bsmith

Asset Table
POCUserName, AssigneeUserName
jdoe,bsmith

My problem arises when I want to display First Name and Last Name for both the POCusername and the AssigneeUserName since FirstName and LastName columns exists once in the Personnel table. How can I display the columns twice. Lets say POCFirstName, POCLastName, AssigneeFirstName, and AssigneeLastName. Or this is something that can't be done.

The query below only display the Assignee info. Of course, I need to something else to display the POC info as well...don't where to go from here...

select tblPersonnel.FirstName, tblPersonnel.LastName, tblAsset.AssigneeUserName, tblAsset.POCUserName
from tblVendor, tblAsset, tblPersonnel
where and tblPersonnel.UserName = tblAsset.AssigneeUserName
and tblAsset.POCUserName in(select tblPersonnel.UserName from tblAsset, tblPersonnel
where tblPersonnel.UserName= tblAsset.POCUserName)

Any help is greatly appreciated

~
N

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-09 : 22:01:33

Try this:

select
pr.FirstName,pr.LastName,a.POCUserName,t.FirsName,t.LastName,a.AssigneeUserName
from dbo.personnel pr
left join dbo.Asset a
on pr.Username = a.pocusername
inner join dbo.personnel t
on a.assigneeusername = t.Username

castroc
Starting Member

2 Posts

Posted - 2008-05-10 : 12:39:25

Thank you very much! this did the trick!

I really appreciate your help!

~
N

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-10 : 14:23:53

you are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources