SQL: select the same column multiple times from a table

Question

I have a table called t_employee with columns ID, NAME

Another table called t_ticket with columns ID, DESCRIPTION, CREATED_BY, UPDATED_BY

Note that CREATED_BY and UPDATED_BY are employee IDs

How can I select such that I get results that go something like:

ID, DESCRIPTION, CREATED_BY, CREATED_BY_NAME, UPDATED_BY, UPDATED_BY_NAME

where CREATED_BY_NAME and UPDATED_BY_NAME are referred from the employee table.

I have done this using a temp table and doing updates, but it seems expensive.

Also the tables are fixed so I have no way of changing them.

Look forward to any suggestions.

Answer 1

Just join the same table multiple times giving it an alias (a different one for each join):

select
  t_ticket.ID, 
  t_ticket.DESCRIPTION, 
  t_ticket.CREATED_BY, 
  e1.name as CREATED_BY_NAME, 
  t_ticket.UPDATED_BY, 
  e2.name as UPDATED_BY_NAME
from
  t_ticket 
left join
  t_employee as e1 on e1.id = t_ticket.CREATED_BY
left join
  t_employee as e2 on e2.id = t_ticket.UPDATED_BY