JOINing the same table

This is probably something simple I'm missing, but here it is anyway.
I have a table called "Employee". Primary key is "pk_EmployeeID".
There is a foreign key field called "fk_SupervisorID" which relates to
pk_EmployeeID. The object is to pull an employee's supervisor from the
same table.
I can't seem to get past a basic SELECT statement to run more complex
queries. Here's what I'm trying:
SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
FROM Employee
' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
Employee.pk_EmployeeID
I've tried inner joins, outer joins, left, right... you name it. The
results I get are always putting the "Employee's" last name in the
Supervisor's (SupLastName) column.
A LEFT JOIN duplicates the supervisors giving me more records than is
actually in the table (which I thought would occur for a RIGHT JOIN)
and a RIGHT JOIN gives me the correct record count, still with botched
name fields. INNER JOIN also botches the name fields but does what it
is supposed to by not including the few records that don't have a
supervisor.
Any ideas?
Thanks in advance!!--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I believe you should have something like this:
CREATE TABLE Employees (
EmployeeID integer not null primary key ,
Name varchar(20) not null ,
- -- ... other columns ...
SupervisorID integer references Employees (EmployeeID)
)
SELECT E.LastName AS EmpLastName, S.LastName AS SupLastName
FROM Employees As E INNER JOIN Employees AS S
ON E.SupervisorID = S.EmployeeID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgfl54echKqOuFEgEQJwFwCggJAWohcqvWQK
QWUNBHdVeliRvDUAoIod
3gHlfBy2yj0p8/J4KRWPjffP
=TMY3
--END PGP SIGNATURE--
Wally wrote:
> This is probably something simple I'm missing, but here it is anyway.
> I have a table called "Employee". Primary key is "pk_EmployeeID".
> There is a foreign key field called "fk_SupervisorID" which relates to
> pk_EmployeeID. The object is to pull an employee's supervisor from the
> same table.
> I can't seem to get past a basic SELECT statement to run more complex
> queries. Here's what I'm trying:
> SELECT Employee.LastName AS EmpLastName, Sup.LastName AS SupLastName
> FROM Employee
> ' JOIN Employee AS Sup ON Sup.fk_SupervisorID =
> Employee.pk_EmployeeID
> I've tried inner joins, outer joins, left, right... you name it. The
> results I get are always putting the "Employee's" last name in the
> Supervisor's (SupLastName) column.
> A LEFT JOIN duplicates the supervisors giving me more records than is
> actually in the table (which I thought would occur for a RIGHT JOIN)
> and a RIGHT JOIN gives me the correct record count, still with botched
> name fields. INNER JOIN also botches the name fields but does what it
> is supposed to by not including the few records that don't have a
> supervisor.|||Are you saying you have a primary key that is been referenced as a
foreignkey in the same table? WHYyyyy' Anyways, to solve your problem
for now, here is the query.....
select E.LastName as EmpLastName,
(Select LastName as SupLastName from Employees Where employeeID =
E.SupervisorID)
from Employee E|||Query Builder wrote:
> Are you saying you have a primary key that is been referenced as a
> foreignkey in the same table? WHYyyyy' Anyways, to solve your
problem
> for now, here is the query.....
No... there is no actual reference between the fields. As far as SQL
Server is concerned, pk_SupervisorID is an indexed primary key.....
and fk_SupervisorID is just another unreferenced column with data in
it. The relation of the two fields only occurs in reports that the
front-end calls for.

> select E.LastName as EmpLastName,
> (Select LastName as SupLastName from Employees
> Where employeeID = E.SupervisorID)
> from Employee E
FYI: Your SELECT statement worked fine, but for some odd reason it
didn't label the SupLastName field.
Thanks!|||> SELECT E.LastName AS EmpLastName, S.LastName
> AS SupLastName
> FROM Employees As E INNER JOIN Employees AS S
> ON E.SupervisorID = S.EmployeeID
That worked too. Thanks!
And as I originally said... it was something small that I was missing.
:-|
Oh well... Thanks again!