January 26, 2006 at 2:00 am
I have a table called logon table which has some columns like userid, Firstname, lastname, email and Parent.
Typically looks like this
userid Firstname lastname email parent
101 Bob Dan dabob@tsa.com 309
102 Riz Bob fler@tsa.com 101
309 roger Brooks rbrot1@tsa.com 106
310 Cavin leo klek2@tsa.com 700
106 Joe Brooks joebrot@tsa.com 701
Here parent is again an id column and this represents the managers id.
I want to write a query which would pull the data in this format.
userid Firstname lastname email FirstName LastName
101 Bob Dan dabob@tsa.com roger Brooks
102 Riz Bob fler@tsa.com Bob Dan
309 roger Brooks rbrot1@tsa.com Joe Brooks
310 Cavin leo klek2@tsa.com San Leo
106 Joe Brooks joebrot@tsa.com Misty June
January 26, 2006 at 2:26 am
You should have another table with the managers id's I called it manager. Then you join like this........
SELECT LOGON.userid,LOGON.Firstname,LOGON.lastname,LOGON.email,MANAGER.FirstName,MANAGER.LastName
FROM LOGON INNER JOIN
MANAGER ON LOGON.managerID = MANAGER.UserID
Andy.
January 26, 2006 at 2:29 am
select l.userid, l.firstname, l.lastname, l.email, p.firstname, p.lastname
from logon l inner join logon p
on l.parent = p.userid
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply