469,106 Members | 2,258 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

How to do this self-join??

al
Greetings,

Would be thankful for your kind help. I have a table (Northwind
employees table). In the employees table, a column called ReportsTo
which lists the EmployeeID, int data type, of the manager of an
employee.This ReportsTo column is basically the EmployeeID column:

EmployeeID Title ReportsTo FirstName SecondName ......
......
1 Manager 4 Steven Buchan
2 Sale Rep. 1 Janet Leverling
3 Clerk 1 Ropert King
4 Presedint Null Andrew Fuller

My question is how can I get the column ReportsTo to include first and
second name, instead of numbers (employeeid).

What I want is something like this(firsname, lastname OR lastname,
firstname for the ReportsTo columen):

EmployeeID Title ReportsTo FirstName SecondName
.... ...
1 Manager Andrew Fuller Steven Buchan
2 Sale Rep. Steven Buchan Janet Leverling
3 Clerk Steven Buchan Ropert King
4 Presedint Null Andrew Fuller

MTIA,
Grawsha
Jul 20 '05 #1
2 4057
"al" <gr*********@yahoo.com> wrote in message
news:66**************************@posting.google.c om...
Greetings,

Would be thankful for your kind help. I have a table (Northwind
employees table). In the employees table, a column called ReportsTo
which lists the EmployeeID, int data type, of the manager of an
employee.This ReportsTo column is basically the EmployeeID column:

EmployeeID Title ReportsTo FirstName SecondName ......
.....
1 Manager 4 Steven Buchan
2 Sale Rep. 1 Janet Leverling
3 Clerk 1 Ropert King
4 Presedint Null Andrew Fuller

My question is how can I get the column ReportsTo to include first and
second name, instead of numbers (employeeid).

What I want is something like this(firsname, lastname OR lastname,
firstname for the ReportsTo columen):

EmployeeID Title ReportsTo FirstName SecondName
... ...
1 Manager Andrew Fuller Steven Buchan
2 Sale Rep. Steven Buchan Janet Leverling
3 Clerk Steven Buchan Ropert King
4 Presedint Null Andrew Fuller

MTIA,
Grawsha


SELECT E1.EmployeeID,
E1.Title,
E2.FirstName + ' ' + E2.LastName AS ReportsTo,
E1.FirstName,
E1.LastName
FROM Northwind..Employees AS E1
LEFT OUTER JOIN
Northwind..Employees AS E2
ON E2.EmployeeID = E1.ReportsTo

Regards,
jag
Jul 20 '05 #2
al
"John Gilson" <ja*@acm.org> wrote in message news:<C9*********************@twister.nyc.rr.com>. ..
"al" <gr*********@yahoo.com> wrote in message
news:66**************************@posting.google.c om...
Greetings,

Would be thankful for your kind help. I have a table (Northwind
employees table). In the employees table, a column called ReportsTo
which lists the EmployeeID, int data type, of the manager of an
employee.This ReportsTo column is basically the EmployeeID column:

EmployeeID Title ReportsTo FirstName SecondName ......
.....
1 Manager 4 Steven Buchan
2 Sale Rep. 1 Janet Leverling
3 Clerk 1 Ropert King
4 Presedint Null Andrew Fuller

My question is how can I get the column ReportsTo to include first and
second name, instead of numbers (employeeid).

What I want is something like this(firsname, lastname OR lastname,
firstname for the ReportsTo columen):

EmployeeID Title ReportsTo FirstName SecondName
... ...
1 Manager Andrew Fuller Steven Buchan
2 Sale Rep. Steven Buchan Janet Leverling
3 Clerk Steven Buchan Ropert King
4 Presedint Null Andrew Fuller

MTIA,
Grawsha


SELECT E1.EmployeeID,
E1.Title,
E2.FirstName + ' ' + E2.LastName AS ReportsTo,
E1.FirstName,
E1.LastName
FROM Northwind..Employees AS E1
LEFT OUTER JOIN
Northwind..Employees AS E2
ON E2.EmployeeID = E1.ReportsTo

Regards,
jag

Jag,

Thanks for your help. Does this solution work with CommandBuilder in ADO.NET?

Grawsha
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Ralf W. Grosse-Kunstleve | last post: by
4 posts views Thread by David Coffin | last post: by
4 posts views Thread by marek.rocki | last post: by
7 posts views Thread by Andrew Robert | last post: by
24 posts views Thread by Peter Maas | last post: by
84 posts views Thread by braver | last post: by
13 posts views Thread by Kurda Yon | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.