CODEWITHSUNDEEP
sql
user2739963
user2739963

Reputation: 75

SQL: select the same column multiple times from a table

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.

Upvotes: 3

Views: 3432

Answers (2)

Tamim Al Manaseer
Tamim Al Manaseer

Reputation: 3724

You need to join twice and use aliases, like so:

SELECT
    t.ID, t.DESCRIPTION, t.CREATED_BY, TableCreated.Name as CREATED_BY_NAME, t.UPDATED_BY, TableUpdated.Name as UPDATED_BY_NAME
FROM 
    t_ticket t INNER JOIN  t_employee TableCreated
    ON t.CreatedBy = TableCreated.Id
    INNER JOIN t_employee TableUpdated
    on t.CreatedBy = TableUpdated.Id;

(use Inner or Left join based on your case)

Upvotes: 0

georstef
georstef

Reputation: 1388

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

Upvotes: 8

Related Questions