Is it possible to join a table to itself

I have a query that joins two tables together. In table O I have an employee ID, which I join to the HR table to retrieve the employee name:

inner join hr AS hr on o.syscreator = hr.res_id

Also on the HR table is the employees manager, but the value is only the employee ID of the manager. Therefore to retrieve the full name of the manager, would I need to use a self join? I could only find examples of self join using the FROM table, in this case it’s the join table.

select o.ordernr,
       o.refer,
       o.orddat,
       o.afldat,
       o.magcode,
       o.user_id,
       o.status, 
       o.represent_id,
       o.crdnr,
       o.ord_debtor_name,
       o.ord_AddressLine1,
       ord_PostCode,
       ord_City,
       ord_StateCode,
       o.ord_landcode,
       o.ord_Phone,
       o.ord_contactperson, 
       o.ord_contactemail,
       o.syscreated,
       h.fullname AS RepName,
       h.repto_id AS ManagerID
from order o
inner join humres AS hr on o.syscreator = hr.res_id

Any help would be appreciated.

Answer

You can sure bring the same table twice – you just need to use a different alias.

I think that you want:

select 
    o.ordernr, 
    ..., 
    h.fullname AS RepName, 
    h.repto_id AS ManagerID,
    hr_mgr.fullname as MgrName
from order o 
inner join humres AS hr on o.syscreator = hr.res_id
inner join humres AS hr_mgr on hr_mgr.res_id = hr.repto_id

Advertisement

Answer