CODEWITHSUNDEEP
sql-servert-sql
David Brierton
David Brierton

Reputation: 7397

SQL Server : joining the users table to match the matching column employee number

I am running a query to show the users name from the users table to the tickets table requester_id, submitter_id, and assignee_id to add a name column behind it like below:

  ,t.requester_id
  ,u.name as requester_name
  ,t.submitter_id
  ,u.name as submitter_name
  ,t.assignee_id
  ,u.name as assignee_name

If I only do the requester_name and join join Users as u on t.requester_id = u.ID it works. But I am trying to do this to three columns. 

SELECT t.id
 ,t.url
 ,t.external_id
 ,t.type
 ,t.subject
 ,t.description
 ,t.priority
 ,t.status
 ,t.recipient
 ,t.requester_id
 ,u.name as requester_name
 ,t.submitter_id
 ,u.name as submitter_name
 ,t.assignee_id
 ,u.name as assignee_name
 ,t.organization_id
 ,t.group_id
 ,t.collaborator_ids
 ,t.forum_topic_id
 ,t.problem_id
 ,t.has_incidents
 ,t.due_at
 ,t.tags
 ,t.via
 ,t.custom_fields
 ,t.satisfaction_rating
 ,t.sharing_agreement_ids
 ,t.followup_ids
 ,t.ticket_form_id
 ,t.created_at
 ,t.updated_at
 ,t.channel
FROM Tickets as t
join Users as u on t.requester_id = u.ID
join Users as u on t.submitter_id = u.ID
join Users as u on t.assignee_id = u.ID

I am trying to add a new row after each requester_id, submitter_id, and assignee_id to show the name after it from the users table.

Any help would be greatly appreciated.

Upvotes: 0

Views: 28

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You are halfway there, by joining to the Users table three times, but you need to give it a different alias each time:

FROM Tickets as t
join Users as ru on t.requester_id = ru.ID
join Users as su on t.submitter_id = su.ID
join Users as au on t.assignee_id = au.ID

Then:

  ,t.requester_id
  ,ru.name as requester_name
  ,t.submitter_id
  ,su.name as submitter_name
  ,t.assignee_id
  ,au.name as assignee_name

Upvotes: 5

Related Questions