Joining tables - 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)

Joining tables

Author

Topic

CSharpNewbie
Starting Member

39 Posts

Posted - 2010-07-26 : 14:13:37

Hi, I have a table with the following rows:

tblEmployee
RecordID, Location.ID, EmployeeID, ManagerID, DirectorID

tblPerson
RecordID, EmployeeID, FirstName, LastName, FullName

I want to return a result that like:
tblEmployee.RecordID, tblEmployee.LocationID, EmployeeID, EmployeeFullName, ManagerID, ManagerFullName, DirectorID, DirectorFullName

How would I join the tables to return those fields?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-26 : 14:26:45

From what i see you have table tblPerson what holds information of the employees but not information of Manager data nor director data.

so try this:


select * 
from tblEmployee as e
inner join tblPerson as p
on e.EmployeeID = p.EmployeeID

please provide also tblManager and tblDirector so we can help you write the whole query.

CSharpNewbie
Starting Member

39 Posts

Posted - 2010-07-26 : 14:30:26

There is no tblManager & tblDirector

e.EmployeeID = p.EmployeeID
e.ManagerID = p.EmployeeID
e.DirectorID = p.EmployeeID

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-26 : 14:32:28

Try something like this:


SELECT E.RecordID, E.LocationID,
       E.EmployeeID, P1.FullName AS EmployeeFullName,
       E.ManagerID, P2.FullName AS ManagerFullName,
       E.DirectorID, P3.FullName AS DirectorFullName
  FROM tblEmployee AS E
       INNER JOIN
       tblPerson AS P1
       ON P1.EmployeeID = E.EmployeeID
       LEFT OUTER JOIN
       tblPerson AS P2
       ON P2.EmployeeID = E.ManagerID
       LEFT OUTER JOIN
       tblPerson AS P3
       ON P3.EmployeeID = E.DirectorID

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-27 : 01:48:35

If you don't have tblManager nor tblDirector than you should normalize your data because of the nature of the data. One can have on hierarchy level only one director, etc. etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources