I have to look up employees one at a time from our employee table - then I have to look up (query) that employee's supervisor - the unique identifier for the first query is EmpID - and when the query returns the record for that employee, it shows the SupID (The employee's supervisor's EmpID) - so I have to make a note of the employee's supervisor's employee ID, then run a query using the original employee's supervisor's employee ID as the criteria.

Is there a way to have the record from tblEmployee for the employee I'm querying on, and include in the record set that employee's supervisor as well ?

I would think there would be, but I can't get the syntax right

Any help would be greatly appreciated.

Steve

I''m not sure if you are saying you want to do this for one specifc employee or for all employees.
For all employees do...


Select E.*, S.*
from Employeetable as E inner join Employeetable as S
on E.SupId = S.EmpId

For a single employee add a where clause..

Where E.empid = 123

or whatever the value is.

Peter - that did not work - to test it, I used my own employee ID - what returned was just one record - the info on me, but I wanted to see 2 records - me and my supervisor - just for grins I tried Left join right join, full outer join, nothing - every time I just got one record - my record.

I suggest you scroll to the right a bit when you look at your result.

Oops - sorry - I wanted it as two separate records, like a subquery - is that possible ?

You can try this, ( but I'm just making it up (:-) )



Select * from Employeetable where EmpId = [Enter EmpID]
Union All
Select * from Employeetable where empid = Dlookup("supId", "employeetable", "Empid= " & [Enter EmpID]

Edit:
Sorry - I forgot you are asking about T_Sql, not Access sql. I don't know what the equivalent to this would be in T_SQL.

That's ok - thanks for your help - I was more trying to learn subqueries - thanks !