Joining a table to itself.

[countdrak]

I have a table with colums, ID,firstname,lastname,supervisorID
So something like this.

ID FirstName LastName SupervisorID
1 John Smith 3
2 Mike Harris 1
3 Chris Small 0


Now I wanna write a query where instead of the supervisorid I can show the firstname and lastname of the supervisor. I want to join the table to itself. So instead of the number it should display john smith supervisor is chris small and mike harris supervisor is john smith. 0 indicates they dont have a supervisor.How do I write this query? I keep getting a cross join when I do a simple join on ID and SupervisorID

 

[r937]

select e.ID
     , e.FirstName
     , e.LastName
     , s.FirstName as SupervisorFirstName
     , s.LastName  as SupervisorLastName
  from yourtable as e
left outer
  join yourtable as s
    on e.SupervisorID
     = s.ID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)