Join query from table with multiple foreign keys to same table primary key

Question

I have a workorder system using SQL Express 2008. I have a table called Workorders that has several personnel that are linked to it via UserID. In the Workorder table I have TechID for the Technician, CustomerID for the Customer, QAID for quality assurance. These are linked back to the User Table via UserID (User Table PK). I want to join the tables to return Technician Name, Customer Name, and QA Name from the User Table and other job information information from the Workorder Table. I have no idea how to construct the join.

Answer 1

Hi,

What about something a bit like this :

select tech.name as tech_name,
    customer.name as customer_name,
    qa.name as qa_name
from Workorders
    inner join User as tech on tech.userId = Workorders.techId
    inner join User as customer on customer.useId = Workorders.CustomerId
    inner join User as qa on qa.useId = Workorders.QAID

(Might need some tunning, but the idea should be here)

ie, you are :

• starting with a workorder
• inner join on its tech guy (a User),
• and then inner joinning on its customer (another user)
• and so on

And this allows you to get each name, using the right alias in the select clause.
Note that I used aliases in the select clause too -- that might be usefull to have "worker_name" and "tech_name", instead of just two columns names "name" -- especially if you are calling this query from some other programming language.

Note : if one of those userId field can be NULL, you might want to use a left join, instead of an inner join.

Answer 2

select tus.Name as 'TechnicianName',
       cus.Name as 'CustomerName',
       qus.Name as 'QaName',
       wod.*
  from WorkOrders wod
         left outer join
       Users      tus on tus.UserId = wod.TechId
         left outer join
       Users      cus on cus.UserId = wod.CustomerId
         left outer join
       Users      qus on qus.UserId = wod.QaId